How to Convert Timezones in MariaDB

Galaxy Glossary

How do I convert timezones in MariaDB?

Use CONVERT_TZ() or SET time_zone to shift DATETIME/TIMESTAMP values between zones in MariaDB.

Sign up for the latest in SQL knowledge from the Galaxy Team!

Thank you! Your submission has been received!

Oops! Something went wrong while submitting the form.

Description

What does CONVERT_TZ() do?

CONVERT_TZ(datetime_expr, from_tz, to_tz) shifts a DATETIME or TIMESTAMP from one zone or offset to another. It returns NULL if either zone is unknown or missing from the time-zone tables.

How do I load time-zone tables?

Run mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql. Without this data, named zones like 'America/New_York' are unresolved and CONVERT_TZ() fails.

How do I set the session time_zone?

SET time_zone = 'UTC'; changes the conversion context for NOW(), CURRENT_TIMESTAMP, and TIMESTAMP arithmetic for the current connection only.

When should I store UTC?

Store all timestamps in UTC for consistency and index efficiency. Convert to the user’s zone at query time with CONVERT_TZ() or by switching @@session.time_zone.

Example: convert order timestamps to customer zone

SELECT o.id, o.order_date AS utc_time, CONVERT_TZ(o.order_date, '+00:00', c_tz.tz) AS local_time FROM Orders o JOIN Customers c ON c.id = o.customer_id JOIN CustomerTimezones c_tz ON c_tz.customer_id = c.id;

How to convert using numeric offsets?

Pass offsets directly: CONVERT_TZ(order_date, '+00:00', '-05:00'). Useful when the client sends only the offset.

How to detect missing zone data?

If CONVERT_TZ() returns NULL, the zone is missing. Filter with WHERE local_time IS NULL and alert or fallback to UTC.

Best practices for ecommerce apps

• Keep DATETIME/TIMESTAMP columns in UTC.
• Store each user’s IANA zone or numeric offset in a profile table.
• Use CONVERT_TZ() in SELECT or in a VIEW, not during INSERT.
• Index UTC columns for date-range searches to stay sargable.

Why How to Convert Timezones in MariaDB is important

How to Convert Timezones in MariaDB Example Usage


-- Show today’s US-Pacific sales total in UTC and local time
SELECT SUM(total_amount)                                        AS total_usd_utc,
       CONVERT_TZ(SUM(total_amount),'UTC','America/Los_Angeles') AS total_usd_pst
FROM   Orders
WHERE  order_date >= DATE_SUB(UTC_TIMESTAMP(), INTERVAL 1 DAY);

How to Convert Timezones in MariaDB Syntax


CONVERT_TZ(datetime_expr, from_tz, to_tz)
  • datetime_expr  – DATETIME or TIMESTAMP value to convert
  • from_tz        – Source zone name (e.g., 'UTC') or offset ('+00:00')
  • to_tz          – Target zone name ('America/New_York') or offset ('-05:00')

-- Set session zone for current connection
SET time_zone = 'zone_name' | '+/-HH:MM';

-- Ecommerce example: convert order_date (stored UTC) to US Eastern
SELECT CONVERT_TZ(order_date,'UTC','America/New_York') AS eastern_time
FROM   Orders;

Common Mistakes

Using named zones without loading mysql.time_zone tables causes CONVERT_TZ() to return NULL. Fix by loading zoneinfo data with mysql_tzinfo_to_sql.
Storing local times directly leads to daylight-saving ambiguity and hard-to-index ranges. Store UTC in the database and convert on output.

Frequently Asked Questions (FAQs)

Does CONVERT_TZ() work on DATE columns?

No. DATE has no time component. Cast to DATETIME or append a time portion first.

Can I change the global server time_zone?

Yes, add default_time_zone='+00:00' in my.cnf or run SET GLOBAL time_zone = '+00:00', but ensure applications expect UTC.

Is daylight-saving handled automatically?

Yes, when you use IANA zone names and the time-zone tables are loaded. Numeric offsets do not adjust automatically for DST.

Want to learn about other SQL terms?

dbt tests

dbt tests are declarative assertions in dbt that automatically validate data quality during model builds.

dbt utils

dbt utils is a community-maintained dbt package that adds time-saving utility macros for analytics engineers, such as generating surrogate keys, safely unioning models, and automating date spines.

dbt Tutorial Glossary: Build, Test & Deploy SQL Models

dbt is an open-source framework that lets data teams transform raw warehouse tables into tested, documented, production-ready datasets using version-controlled SQL.