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.