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

Need a 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

Need a 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

Need a 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

Need a hint?

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