Time zone handling in MySQL - Mini Project: Build & Apply

Time Zone Handling in MySQL

📖 Scenario: You are managing a global events database. Each event is stored with a UTC timestamp, but users want to see event times in their local time zones.

🎯 Goal: Build a MySQL setup that stores event times in UTC and converts them to a specified time zone when queried.

📋 What You'll Learn

Create a table called events with columns id, event_name, and event_time_utc.

Insert sample events with UTC timestamps.

Set a variable for the desired time zone.

Write a query that converts event_time_utc to the specified time zone using CONVERT_TZ().

💡 Why This Matters

🌍 Real World

Many applications store timestamps in UTC to avoid confusion and convert to local time zones for user display.

💼 Career

Understanding time zone handling is essential for database administrators and backend developers working with global data.

1

Create the events table with UTC timestamps

Create a table called events with columns id as INT primary key, event_name as VARCHAR(50), and event_time_utc as DATETIME to store event times in UTC.

MySQL

-- Your code here to create the events table

Hint

Use CREATE TABLE with the specified columns and types.

2

Insert sample events with UTC timestamps

Insert two rows into the events table with id values 1 and 2, event_name values 'Morning Meeting' and 'Evening Webinar', and event_time_utc values '2024-06-01 08:00:00' and '2024-06-01 18:00:00' respectively.

MySQL

CREATE TABLE events (
  id INT PRIMARY KEY,
  event_name VARCHAR(50),
  event_time_utc DATETIME
);
-- Your code here to insert sample events

Hint

Use INSERT INTO events with the exact values given.

3

Set the desired time zone variable

Set a MySQL user-defined variable called @user_timezone to the string value 'America/New_York' to represent the user's local time zone.

MySQL

CREATE TABLE events (
  id INT PRIMARY KEY,
  event_name VARCHAR(50),
  event_time_utc DATETIME
);

INSERT INTO events (id, event_name, event_time_utc) VALUES
(1, 'Morning Meeting', '2024-06-01 08:00:00'),
(2, 'Evening Webinar', '2024-06-01 18:00:00');

-- Your code here to set the @user_timezone variable

Hint

Use SET @user_timezone = 'America/New_York'; to define the variable.

4

Query events converting UTC to user time zone

Write a SELECT query to retrieve id, event_name, and the event time converted from UTC to the user's time zone stored in @user_timezone. Use CONVERT_TZ(event_time_utc, '+00:00', @user_timezone) and alias it as event_time_local.

MySQL

CREATE TABLE events (
  id INT PRIMARY KEY,
  event_name VARCHAR(50),
  event_time_utc DATETIME
);

INSERT INTO events (id, event_name, event_time_utc) VALUES
(1, 'Morning Meeting', '2024-06-01 08:00:00'),
(2, 'Evening Webinar', '2024-06-01 18:00:00');

SET @user_timezone = 'America/New_York';

-- Your code here to select events with converted local time

Hint

Use CONVERT_TZ() with the correct parameters and alias the result as event_time_local.

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