How to store a datetime in MySQL with timezone info

Storing date and time with time zone information in MySQL can be handled in several approaches. Below, I will detail several common methods with examples of implementation and usage.

1. Using TIMESTAMP Type and Setting Time Zone

The TIMESTAMP data type in MySQL automatically converts stored time values to UTC and converts them back to the current time zone setting upon retrieval. This ensures that if your application runs across multiple time zones, using the TIMESTAMP type processes all date and time values uniformly using UTC.

Example:

Suppose we need to store a meeting time and ensure that regardless of where the user is located, they can correctly view the meeting time in their local time.

First, set the MySQL time zone:

sql
SET time_zone = '+00:00';  -- Set to UTC

Then, create a table with a TIMESTAMP field:

sql
CREATE TABLE meetings (
  id INT PRIMARY KEY,
  meeting_time TIMESTAMP
);

When inserting data, you only need to provide the local time; MySQL automatically converts it to UTC for storage.

Upon retrieval, MySQL automatically converts the time back to the current time zone setting:

sql
SELECT meeting_time FROM meetings;

2. Storing Time Zone Information

If you want to store both the specific date and time and time zone information in the database, you can add an additional column to store the time zone.

Example:

When creating the table, include an additional timezone column to store the time zone information:

sql
CREATE TABLE events (
  id INT PRIMARY KEY,
  event_time DATETIME,
  timezone VARCHAR(10)
);

When inserting data, insert both the time and the corresponding time zone:

sql
INSERT INTO events (id, event_time, timezone) VALUES
(1, '2023-07-18 14:00:00', '+02:00');

Upon retrieval, you can use the CONVERT_TZ() function to convert the time zone to the user's local time:

sql
SELECT CONVERT_TZ(event_time, timezone, '+09:00') AS local_time FROM events WHERE id = 1;

This converts the event time from the stored time zone ('+02:00') to Tokyo time ('+09:00').

3. Using UTC Time for Storage

A simple and effective strategy is to store all date and time data as UTC and handle time zone conversions at the application layer.

Example:

When creating the table, use the DATETIME type:

sql
CREATE TABLE logs (
  id INT PRIMARY KEY,
  log_time DATETIME
);

When inserting data, ensure it is converted to UTC:

sql
INSERT INTO logs (id, log_time) VALUES
(1, CONVERT_TZ('2023-07-18 17:00:00', '+08:00', '+00:00'));

The application is responsible for converting the UTC time to the user's local time when displaying the data.

Conclusion

Choose the appropriate strategy based on your application's needs. For handling multi-time zone data, it is recommended to use TIMESTAMP or store both date/time and time zone information. If your application's logic is sufficiently centralized, using UTC time and handling time zone conversions at the application layer is also an effective strategy.