Time zone handling in MySQL - Step-by-Step Execution

Concept Flow - Time zone handling

Start: Input datetime

↓

Check if timezone specified

↓

Convert to UTC

↓

Store or display datetime

↓

End

This flow shows how MySQL handles datetime values by checking for time zone info, converting to UTC if needed, then storing or displaying the time.

Execution Sample

MySQL

SET time_zone = '+00:00';
SELECT CONVERT_TZ('2024-06-01 12:00:00', '+00:00', '+05:30') AS converted_time;

This code sets the session time zone to UTC and converts a datetime from UTC to India Standard Time (+05:30).

Execution Table

Step	Action	Input datetime	From Time Zone	To Time Zone	Resulting datetime
1	Set session time_zone	N/A	N/A	+00:00 (UTC)	Session time_zone set to +00:00
2	Convert datetime	2024-06-01 12:00:00	+00:00	+05:30	2024-06-01 17:30:00
3	Display result	N/A	N/A	N/A	2024-06-01 17:30:00

💡 Conversion complete; datetime adjusted from UTC to +05:30 time zone.

Variable Tracker

Variable	Start	After Step 1	After Step 2	Final
time_zone	system default	+00:00	+00:00	+00:00
input_datetime	N/A	2024-06-01 12:00:00	2024-06-01 12:00:00	2024-06-01 12:00:00
converted_datetime	N/A	N/A	2024-06-01 17:30:00	2024-06-01 17:30:00

Key Moments - 2 Insights

Why does the converted time show 17:30 instead of 12:00?

Because the conversion adds 5 hours and 30 minutes to the original UTC time, shifting it to the +05:30 time zone as shown in execution_table row 2.

What happens if no time zone is specified in CONVERT_TZ?

MySQL uses the session or system time zone by default, so the conversion might not change the time, as implied in the concept_flow's 'No' branch.

Visual Quiz - 3 Questions

Look at the execution_table, what is the converted datetime at step 2?

A 2024-06-01 12:00:00

B 2024-06-01 17:30:00

C 2024-06-01 07:30:00

D 2024-06-01 05:30:00

💡 Hint

Check the 'Resulting datetime' column in execution_table row 2.

At which step is the session time zone set to UTC?

A Step 1

B Step 3

C Step 2

D No step sets it

💡 Hint

Look at the 'Action' and 'To Time Zone' columns in execution_table row 1.

If the 'To Time Zone' was '+00:00' instead of '+05:30', what would the converted datetime be at step 2?

A 2024-06-01 17:30:00

B 2024-06-01 07:30:00

C 2024-06-01 12:00:00

D 2024-06-01 05:30:00

💡 Hint

Converting from '+00:00' to '+00:00' means no time change; see variable_tracker for converted_datetime.

Concept Snapshot

MySQL Time Zone Handling:
- Use SET time_zone to set session time zone.
- Use CONVERT_TZ(datetime, from_tz, to_tz) to convert times.
- If no time zone given, server/session time zone applies.
- Times are stored in UTC internally.
- Always specify time zones to avoid confusion.

Full Transcript

This lesson shows how MySQL handles time zones by setting a session time zone and converting datetime values between zones. The example sets the session time zone to UTC and converts a datetime from UTC to India Standard Time (+05:30). The execution table traces each step: setting the time zone, converting the datetime, and displaying the result. Variables like time_zone and converted_datetime change accordingly. Key moments clarify why the time changes and what happens if no time zone is specified. The quiz tests understanding of the conversion steps and results. Remember, MySQL stores times in UTC internally and converting between zones adjusts the displayed time accordingly.

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