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:<br>SELECT @@global.time_zone;<br>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.