Time zone handling in MySQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is the purpose of time zone handling in MySQL?

Time zone handling ensures that date and time values are stored, retrieved, and displayed correctly according to different geographic locations.

beginner

How do you set the time zone for the current MySQL session?

Use the command: SET time_zone = 'timezone'; For example, SET time_zone = '+00:00'; sets UTC time zone for the session.

intermediate

What is the difference between TIMESTAMP and DATETIME types in MySQL regarding time zones?

TIMESTAMP stores values in UTC and converts to the session time zone on retrieval. DATETIME stores values as-is without time zone conversion.

beginner

How can you check the current global and session time zones in MySQL?

Run these queries:
SELECT @@global.time_zone;
SELECT @@session.time_zone;

beginner

Why is it recommended to store timestamps in UTC in databases?

Storing timestamps in UTC avoids confusion and errors when users or applications in different time zones access the data. It ensures consistent and reliable time data.

Which MySQL data type automatically converts stored time to UTC and back to session time zone on retrieval?

A TIMESTAMP

B DATETIME

C TIME

D DATE

TIMESTAMP stores values in UTC and converts them based on the session time zone.

How do you set the global time zone in MySQL?

A SET time_zone = 'timezone';

B ALTER DATABASE time_zone = 'timezone';

C SET GLOBAL time_zone = 'timezone';

D SET SESSION time_zone = 'timezone';

Use SET GLOBAL time_zone = 'timezone'; to set the global time zone.

What happens if you store a date/time value in a DATETIME column in MySQL?

A It is stored in UTC and converted on retrieval.

B It is stored as-is without time zone conversion.

C It is automatically converted to the server's time zone.

D It is rejected if not in UTC.

DATETIME stores the value exactly as given, without any time zone conversion.

Which command shows the current session time zone in MySQL?

A SHOW VARIABLES LIKE 'time_zone';

B SHOW TIMEZONE;

C SELECT @@global.time_zone;

D SELECT @@session.time_zone;

SELECT @@session.time_zone; returns the current session's time zone.

Why is it important to load time zone tables in MySQL?

A To enable time zone conversions for TIMESTAMP data.

B To speed up queries.

C To allow storing dates before 1970.

D To enable encryption of date/time data.

Time zone tables provide the data needed for MySQL to convert times correctly between zones.

Explain how MySQL handles time zones when storing and retrieving TIMESTAMP and DATETIME values.

Think about how time zone affects storage and display differently for these types.

Describe the steps to set and check the time zone for a MySQL session.

Remember session vs global settings.

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