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.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

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.