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.