Time zone handling in MySQL

Introduction

Time zone handling helps you store and show dates and times correctly for people in different places.

You want to save the exact time an event happened, no matter where the user is.

You need to show the time of a meeting in the viewer's local time zone.

You want to compare times from users in different countries.

You are logging actions and want to keep them in a standard time.

You want to convert stored times to different time zones for reports.

Syntax

MySQL

CONVERT_TZ(date_time, from_time_zone, to_time_zone)

Use named time zones like 'UTC', 'America/New_York', or offsets like '+00:00'.

Make sure your MySQL time zone tables are loaded for named zones to work.

Examples

Converts noon UTC to New York time.

MySQL

SELECT CONVERT_TZ('2024-06-01 12:00:00', 'UTC', 'America/New_York');

Converts UTC time to India Standard Time (+5:30).

MySQL

SELECT CONVERT_TZ('2024-06-01 12:00:00', '+00:00', '+05:30');

Shows current system time and converts it to UTC.

MySQL

SELECT NOW(), CONVERT_TZ(NOW(), 'SYSTEM', 'UTC');

Sample Program

This query shows the same time in Los Angeles, then converts it to UTC and Tokyo time zones.

MySQL

SELECT
  'Original time' AS description, '2024-06-01 15:00:00' AS time_value
UNION ALL
SELECT
  'Time in UTC', CONVERT_TZ('2024-06-01 15:00:00', 'America/Los_Angeles', 'UTC')
UNION ALL
SELECT
  'Time in Asia/Tokyo', CONVERT_TZ('2024-06-01 15:00:00', 'America/Los_Angeles', 'Asia/Tokyo');

Important Notes

If CONVERT_TZ returns NULL, your MySQL time zone tables might not be loaded.

Use UTC to store times in the database to avoid confusion.

Always convert times to the user's local time zone when showing data.

Summary

Time zone handling helps keep times correct across different places.

Use CONVERT_TZ to change times between zones.

Store times in UTC and convert when displaying to users.