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.