How to store a datetime in MySQL with timezone info

在MySQL中存储带有时区信息的日期时间可以采用几种策略。下面我将详细介绍几种常用的方法，并举例说明如何实现和使用。

MySQL中的TIMESTAMP数据类型自动将存储的时间值转换为UTC，并在检索时自动转换回当前的时区设置。这意味着如果你的应用程序在多个时区中运行，使用TIMESTAMP类型可以确保所有日期时间值都是以统一的标准（UTC）来处理。

例子：

假设我们需要存储一个会议的时间，并希望确保无论用户位于何处，都可以正确地看到会议在其本地时间的对应时间。

首先，设置MySQL的时区：

sql
SET time_zone = '+00:00';  -- 设置为UTC

然后，创建包含TIMESTAMP字段的表：

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

插入数据时，你只需要插入本地时间，MySQL会自动将时间转换为UTC存储。

检索数据时，MySQL会根据当前设置的时区自动转换时间：

sql
SELECT meeting_time FROM meetings;

如果你想在数据库中同时保存具体的日期时间和时区信息，可以选择增加一个额外的列来存储时区。

例子：

创建表时，加入一个额外的timezone字段来存储时区信息：

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

插入数据时，同时写入时间和对应的时区：

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

检索数据时，可以使用CONVERT_TZ()函数来转换时区，显示为用户的本地时区：

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

这里将事件时间从存储的时区('+02:00')转换到东京时间('+09:00')。

一种简单而有效的策略是存储所有日期时间数据为UTC，并在应用层处理时区转换。

例子：

创建表时使用DATETIME类型：

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

插入数据时，确保转换为UTC：

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

应用程序在显示数据时负责将UTC时间转换为用户的本地时区。

根据应用的需求选择合适的策略，如果需要处理多时区数据，推荐使用TIMESTAMP或同时存储日期时间和时区信息。如果应用的逻辑足够集中，使用UTC时间并在应用层处理时区也是一种有效的策略。

2024年8月6日 23:52 回复

1个答案