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.

Practice

1. What is the main reason to use time zone handling in MySQL databases?

easy

A. To reduce database size

B. To speed up query execution

C. To keep times correct across different locations

D. To encrypt date and time values

Solution

1. Step 1: Understand the purpose of time zone handling

   Time zone handling ensures that date and time values are accurate regardless of where users or servers are located.

2. Step 2: Identify the correct reason

   Keeping times correct across different locations is the main goal, not performance or encryption.

3. Final Answer:

   To keep times correct across different locations -> Option C

4. Quick Check:

   Time zone handling = keep times correct [OK]

Hint: Time zones fix time differences across places [OK]

Common Mistakes:

• Thinking time zones speed up queries
• Confusing time zones with encryption
• Believing time zones reduce data size

2. Which MySQL function is used to convert a datetime value from one time zone to another?

easy

A. NOW()

B. DATE_FORMAT()

C. TIMESTAMPDIFF()

D. CONVERT_TZ()

Solution

1. Step 1: Recall MySQL time zone functions

   CONVERT_TZ() changes a datetime from one time zone to another. DATE_FORMAT() formats dates, TIMESTAMPDIFF() calculates differences, NOW() returns current time.

2. Step 2: Select the function that converts time zones

   Only CONVERT_TZ() performs time zone conversion.

3. Final Answer:

   CONVERT_TZ() -> Option D

4. Quick Check:

   Convert time zones = CONVERT_TZ() [OK]

Hint: Use CONVERT_TZ() to switch time zones [OK]

Common Mistakes:

• Using DATE_FORMAT() to convert time zones
• Confusing NOW() with conversion
• Trying TIMESTAMPDIFF() for conversion

3. Given the query:
SELECT CONVERT_TZ('2024-06-01 12:00:00', 'UTC', 'America/New_York');
What is the expected output if New York is UTC-4 during daylight saving?

medium

A. '2024-06-01 16:00:00'

B. '2024-06-01 08:00:00'

C. '2024-06-01 12:00:00'

D. NULL

Solution

1. Step 1: Understand the time zones involved

   The input time is '2024-06-01 12:00:00' in UTC. New York is UTC-4 during daylight saving time.

2. Step 2: Calculate the converted time

   Subtract 4 hours from 12:00:00 to get 08:00:00 in New York time.

3. Final Answer:

   '2024-06-01 08:00:00' -> Option B

4. Quick Check:

   UTC 12:00 - 4 hours = 08:00 [OK]

Hint: Subtract offset hours when converting from UTC [OK]

Common Mistakes:

• Adding hours instead of subtracting
• Ignoring daylight saving offset
• Expecting NULL without time zone tables loaded

4. You run this query:
SELECT CONVERT_TZ('2024-06-01 12:00:00', 'UTC', 'Invalid/Zone');
What will happen?

medium

A. Returns NULL

B. Returns the original datetime '2024-06-01 12:00:00'

C. Throws a syntax error

D. Converts to local server time

Solution

1. Step 1: Understand CONVERT_TZ behavior with invalid zones

   If the time zone name is invalid or not found, CONVERT_TZ returns NULL instead of an error or original value.

2. Step 2: Confirm expected output

   Since 'Invalid/Zone' is not a valid time zone, the function returns NULL.

3. Final Answer:

   NULL -> Option A

4. Quick Check:

   Invalid zone = NULL result [OK]

Hint: Invalid zones cause NULL, not errors [OK]

Common Mistakes:

• Expecting syntax error for invalid zone
• Thinking it returns original datetime
• Assuming conversion to server time

5. You want to store all timestamps in UTC but display them in the user's local time zone. Which approach is best in MySQL?

hard

A. Store timestamps in UTC and use CONVERT_TZ() to convert when displaying

B. Store timestamps in local time and convert to UTC when querying

C. Store timestamps without time zone info and rely on client conversion

D. Store timestamps as strings and parse time zones in application code

Solution

1. Step 1: Understand best practice for time zone storage

   Storing timestamps in UTC avoids confusion and inconsistency across users and servers.

2. Step 2: Use CONVERT_TZ() for display

   When showing times to users, convert UTC to their local time zone using CONVERT_TZ().

3. Final Answer:

   Store timestamps in UTC and use CONVERT_TZ() to convert when displaying -> Option A

4. Quick Check:

   Store UTC, convert on display = best practice [OK]

Hint: Store UTC, convert on display with CONVERT_TZ() [OK]

Common Mistakes:

• Storing local time causing confusion
• Relying on client for conversion only
• Storing timestamps as strings losing precision