Time zone handling in MySQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to get the current time in UTC.

MySQL

SELECT [1]();

Drag options to blanks, or click blank then click option'

ANOW

BCURRENT_DATE

CUTC_TIMESTAMP

DSYSDATE

Attempts:

3 left

💡 Hint

Common Mistakes

Using NOW() returns local server time, not UTC.

Using CURRENT_DATE returns only the date, not time.

The UTC_TIMESTAMP() function returns the current UTC date and time.

2fill in blank

medium

Complete the code to convert a datetime from UTC to 'America/New_York' time zone.

MySQL

SELECT CONVERT_TZ('2024-06-01 12:00:00', [1], 'America/New_York');

Drag options to blanks, or click blank then click option'

A'LOCALTIME'

B'SYSTEM'

C'GMT'

D'UTC'

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'SYSTEM' assumes server local time, which may not be UTC.

Using 'LOCALTIME' is not a valid time zone name here.

The second argument of CONVERT_TZ is the source time zone. Since the datetime is in UTC, use 'UTC'.

3fill in blank

hard

Fix the error in the code to get the current time in the 'Asia/Tokyo' time zone.

MySQL

SELECT CONVERT_TZ(NOW(), [1], 'Asia/Tokyo');

Drag options to blanks, or click blank then click option'

A'SYSTEM'

B'UTC'

C'Asia/Tokyo'

D'GMT'

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'UTC' assumes NOW() returns UTC, which it does not.

Using 'Asia/Tokyo' as source time zone causes no change.

Since NOW() returns the server's local time, the source time zone should be 'SYSTEM' to convert correctly to 'Asia/Tokyo'.

4fill in blank

hard

Fill both blanks to get the current time in UTC and convert it to 'Europe/London' time zone.

MySQL

SELECT CONVERT_TZ([1](), [2], 'Europe/London');

Drag options to blanks, or click blank then click option'

AUTC_TIMESTAMP

B'UTC'

C'SYSTEM'

DNOW

Attempts:

3 left

💡 Hint

Common Mistakes

Using NOW() instead of UTC_TIMESTAMP() returns local time.

Using 'SYSTEM' as source time zone when time is UTC.

Use UTC_TIMESTAMP() to get current UTC time and specify source time zone as 'UTC' for conversion.

5fill in blank

hard

Fill all three blanks to create a query that converts '2024-06-01 15:00:00' from 'Europe/Paris' to 'Asia/Kolkata' and formats the output as 'YYYY-MM-DD HH:MM:SS'.

MySQL

SELECT DATE_FORMAT(CONVERT_TZ('2024-06-01 15:00:00', [1], [2]), [3]);

Drag options to blanks, or click blank then click option'

A'Europe/Paris'

B'Asia/Kolkata'

C'%Y-%m-%d %H:%i:%s'

D'%d-%m-%Y %H:%i:%s'

Attempts:

3 left

💡 Hint

Common Mistakes

Swapping source and target time zones.

Using wrong format string that changes date order.

The first two blanks are the source and target time zones. The third blank is the format string for standard datetime output.

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