How to Perform Timezone Conversion in MySQL

Galaxy Glossary

How do I use MySQL’s CONVERT_TZ() to convert timestamps between time zones?

Use CONVERT_TZ() or SET time_zone to shift DATETIME/TIMESTAMP values between IANA or offset-based time zones.

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 time zone to another and returns a new value. The original column stays unchanged, making it safe for reporting or display.

How do I prepare time zone tables?

MySQL uses internal zone tables. If they are empty, CONVERT_TZ returns NULL.Load them once: mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql, then run FLUSH PRIVILEGES.

How do I list available zones?

Execute SELECT Name FROM mysql.time_zone_name ORDER BY Name; or simply run SHOW VARIABLES LIKE 'system_time_zone'; to see the server’s default zone.

How do I convert order timestamps to UTC?

Run: SELECT id, CONVERT_TZ(order_date,'America/New_York','UTC') AS order_date_utc FROM Orders; This produces UTC values you can feed into analytics without daylight-saving inconsistencies.

How can I display times in a user’s zone?

Store timestamps in UTC, pass the user’s IANA zone through a session variable, then convert on SELECT: SELECT id, CONVERT_TZ(order_date,'UTC',@user_tz) AS local_time FROM Orders;.

How do I change the session time zone?

Issue SET time_zone='America/Los_Angeles'; All subsequent TIMESTAMP values are assumed to be in that zone when stored and converted back on retrieval.

When should I avoid storing local time?

Local storage complicates daylight-saving transitions.Keeping all data in UTC avoids ambiguous or missing hours and simplifies cross-region queries.

Best practice summary

Store UTC in tables, validate zone tables, convert only at the edge, and cache frequent CONVERT_TZ results in views or materialized reports for performance.

.

Why How to Perform Timezone Conversion in MySQL is important

How to Perform Timezone Conversion in MySQL Example Usage


-- Convert order times to a customer’s local zone and filter a date range
SET @user_tz = 'America/Chicago';
SELECT c.name,
       o.id,
       CONVERT_TZ(o.order_date,'UTC',@user_tz) AS order_local,
       o.total_amount
FROM   Customers  c
JOIN   Orders     o ON o.customer_id = c.id
WHERE  o.order_date >= CONVERT_TZ('2023-10-01 00:00:00',@user_tz,'UTC');

How to Perform Timezone Conversion in MySQL Syntax


CONVERT_TZ(datetime_expr, from_tz, to_tz)
    datetime_expr : DATETIME | TIMESTAMP column or literal
    from_tz       : IANA zone name (e.g., 'UTC') or offset ('+00:00')
    to_tz         : IANA zone name or offset

SET [GLOBAL | SESSION] time_zone = { tz_name | tz_offset }

Example (ecommerce):
SELECT id,
       CONVERT_TZ(order_date,'America/New_York','UTC') AS order_date_utc
FROM   Orders;

SET SESSION time_zone = 'America/Los_Angeles';

Common Mistakes

Relying on CONVERT_TZ() without loading zone tables—this returns NULL. Fix by running mysql_tzinfo_to_sql and FLUSH PRIVILEGES once per server.
Storing local time in DATETIME expecting automatic conversion. DATETIME is zone-agnostic; use TIMESTAMP or store UTC and convert on SELECT.

Frequently Asked Questions (FAQs)

Does CONVERT_TZ work with UNIX timestamps?

Convert the integer to DATETIME with FROM_UNIXTIME(), then pass it to CONVERT_TZ. Example: CONVERT_TZ(FROM_UNIXTIME(unixts),'UTC','Asia/Tokyo').

How do I update a column in place?

Run an UPDATE: UPDATE Orders SET order_date = CONVERT_TZ(order_date,'America/New_York','UTC'); Always back up first.

Why does CONVERT_TZ return NULL?

Either the zone name is misspelled or the mysql.time_zone tables are empty. Verify with SELECT COUNT(*) FROM mysql.time_zone_name;.

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.