Time zone handling in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Convert UTC timestamp to local time

Given a UTC timestamp '2024-06-01 12:00:00', which query correctly converts it to 'America/New_York' time zone?

MySQL

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

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

B SELECT CONVERT_TZ('2024-06-01 12:00:00', 'UTC', 'Europe/London') AS local_time;

C SELECT CONVERT_TZ('2024-06-01 12:00:00', 'GMT', 'EST') AS local_time;

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

💡 Hint

Remember that CONVERT_TZ(source_time, from_tz, to_tz) changes time from one zone to another.

Option A correctly converts from UTC to America/New_York. Option A reverses the zones. Option A uses abbreviations that may not be recognized. Option A converts to London time, not New York.

🧠 Conceptual

intermediate

Understanding MySQL time zone storage

Which statement about MySQL TIMESTAMP and DATETIME types regarding time zones is true?

A Both TIMESTAMP and DATETIME store values as-is without any time zone conversion.

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

C Both TIMESTAMP and DATETIME store values in UTC and convert on retrieval.

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

💡 Hint

Think about how MySQL handles time zone conversion internally for these types.

TIMESTAMP values are stored in UTC internally and converted to the current session time zone when retrieved. DATETIME values are stored exactly as given without conversion.

📝 Syntax

advanced

Identify the syntax error in time zone setting

Which option contains a syntax error when setting the time zone in MySQL?

MySQL

SET time_zone = 'America/Los_Angeles';

A SET time_zone = America/Los_Angeles;

B SET time_zone = 'America/Los_Angeles';

C SET time_zone = '+08:00';

D SET time_zone = '-05:00';

💡 Hint

Check if string literals are properly quoted.

Option A lacks quotes around the time zone name, causing a syntax error. Options B, C, and D are valid.

❓ optimization

advanced

Optimizing queries with time zone conversion

You have a large table with UTC timestamps. Which approach is best to efficiently query rows within a local time range in 'Europe/Berlin' time zone?

A Use CONVERT_TZ on the timestamp column in the WHERE clause to compare with local times.

B Convert the local time range to UTC once, then query the UTC timestamp column using BETWEEN.

C Store timestamps as DATETIME in local time and query directly without conversion.

D Use UNIX_TIMESTAMP on the timestamp column and compare with UNIX timestamps of local times.

💡 Hint

Consider how functions in WHERE clauses affect index usage.

Option B allows use of indexes on the timestamp column by converting the query range once. Option B applies a function on the column, preventing index use and slowing queries.

🔧 Debug

expert

Debugging unexpected time zone conversion results

A query uses CONVERT_TZ but returns NULL for all rows. What is the most likely cause?

A The session time zone is set to SYSTEM.

B The input timestamp format is invalid.

C The MySQL time zone tables are not loaded or incomplete.

D The CONVERT_TZ function is deprecated and disabled.

💡 Hint

CONVERT_TZ returns NULL if it cannot find the time zone names.

If the time zone tables are not loaded, named time zones are unknown and CONVERT_TZ returns NULL. Invalid input format causes errors, not NULL. SYSTEM time zone does not cause NULL. CONVERT_TZ is not deprecated.

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