Introduction:

In this blog, we will explore various methods to perform UTC to local timezone conversion in MySQL, a widely-used relational database management system. 

Method 1: Using TIMESTAMP WITH TIME ZONE (MySQL 8.0 and above)

-- Create a table with a TIMESTAMP WITH TIME ZONE column
CREATE TABLE events (
    event_id INT PRIMARY KEY,
    event_name VARCHAR(255),
    event_timestamp TIMESTAMP WITH TIME ZONE
);

-- Insert data with UTC timestamps
INSERT INTO events (event_id, event_name, event_timestamp)
VALUES
    (1, 'Event 1', '2023-08-04 12:00:00 UTC'),
    (2, 'Event 2', '2023-08-04 15:30:00 UTC');

In this method, we create a table with a column of type TIMESTAMP WITH TIME ZONE to store timezone-aware timestamps. By inserting data with UTC timestamps explicitly, MySQL automatically handles the conversion to the appropriate local timezone when retrieving the data. This method is suitable for MySQL 8.0 and above, as it natively supports timezone-aware data types.

Output:

Method 2: Using CONVERT_TZ() Function

-- Create a table to store events
CREATE TABLE events (
    event_id INT PRIMARY KEY,
    event_name VARCHAR(255),
    event_timestamp DATETIME
);

-- Insert data with UTC timestamps
INSERT INTO events (event_id, event_name, event_timestamp)
VALUES
    (1, 'Event 1', '2023-08-04 12:00:00'),
    (2, 'Event 2', '2023-08-04 15:30:00');

In this method, we create a table with a DATETIME column to store timestamps without timezone information. To convert UTC timestamps to the local timezone, we use the CONVERT_TZ() function in the SELECT query:

SELECT event_id, event_name, CONVERT_TZ(event_timestamp, 'UTC', 'America/New_York') AS local_timestamp
FROM events;

Output:

Method 3: Using Session Timezone Setting

-- Set the session timezone to America/New_York
SET time_zone = 'America/New_York';

-- Retrieve data with UTC timestamps
SELECT event_id, event_name, event_timestamp FROM events;

In this method, we set the session timezone to the desired local timezone using the SET time_zone statement. MySQL will automatically convert UTC timestamps to the local timezone for that particular session.

Output:

Conclusion:

In this blog, we explored multiple methods to convert UTC to the local timezone in MySQL. The TIMESTAMP WITH TIME ZONE data type simplifies timezone handling for MySQL 8.0 and above, while the CONVERT_TZ() function serves as a reliable option for older versions. Additionally, we learned how to utilize session timezone settings for automatic conversions.