如何在MySQL中存储带有时区信息的日期时间

Question

我有数千张照片是在坦桑尼亚拍摄的，我想将每张照片的拍摄日期和时间存储在MySQL数据库中。但是，服务器位于美国，当我尝试在春季夏令时（在美国）期间存储位于"无效"小时内的坦桑尼亚日期时间时，我遇到了问题。坦桑尼亚不执行 DST，因此该时间是实际有效的时间。

其他复杂情况是，有来自许多不同时区的协作者需要访问存储在数据库中的日期时间值。我希望他们总是以坦桑尼亚时间出现，而不是在各种合作者所在的当地时间。

我不愿意设置会话时间，

因为我知道当有人有时忘记设置会话时间并且超时都出错时，会出现问题。而且我无权更改有关服务器的任何内容。

我读过：夏令时和时区最佳实践和MySQL 日期时间字段和夏令时 -- 如何引用"额外"小时？和在 PHP/MySQL 中将日期时间存储为 UTC

但它们似乎都没有解决我的特定问题。我不是SQL专家;有没有办法在设置日期时间时指定时区？我没见过。否则，非常感谢有关如何解决此问题的任何建议。

编辑：这是我遇到的问题的一个例子。我发送命令：

INSERT INTO Images (CaptureEvent, SequenceNum, PathFilename, TimestampJPG) 
VALUES (122,1,"S2/B04/B04_R1/IMAG0148.JPG","2011-03-13 02:49:10")

我收到错误：

Error 1292: Incorrect datetime value: '2011-03-13 02:49:10' for column 'TimestampJPG'

此日期和时间存在于坦桑尼亚，但不存在于数据库所在的美国。

Answer 1

你说：

我希望他们总是以坦桑尼亚时间出现，而不是在各种合作者所在的当地时间。

如果是这种情况，则不应使用 UTC。 您需要做的就是在MySQL中使用DATETIME类型而不是TIMESTAMP类型。

来自 MySQL 文档：

MySQL将TIMESTAMP值从当前时区转换为UTC以进行存储，并从UTC转换回当前时区以进行检索。（对于其他类型（如 DATETIME）不会发生这种情况。

如果您已经在使用 DATETIME 类型，则一定不是在本地时间开始设置它。 您需要减少对数据库的关注，而更多地关注应用程序代码 - 此处未显示。 问题和解决方案将因语言而异，因此请务必使用应用程序代码的相应语言标记问题。

Answer 2

这里的答案都没有一针见血。

如何使用时区信息在 MySQL 中存储日期时间

使用两列：DATETIME 和 VARCHAR 来保存时区信息，这些信息可能采用多种形式：

时区或位置（如 America/New_York）是最高的数据保真度。

时区缩写（如 PST）是下一个最高保真度。

在这方面，诸如

-2:00 这样的时间偏移量是最小的数据量。

一些关键点：

• 避免TIMESTAMP，因为它仅限于 2038 年，并且 MySQL 将其与服务器时区相关联，这可能是不希望的。
• 不应将时间偏移量天真地存储在INT字段中，因为存在半小时和四分之一小时的偏移量。

---

如果您的用例让MySQL按时间顺序比较或排序这些日期很重要，那么DATETIME有一个问题：

就"即时时间"而言，'2009-11-10 11:00:00 -0500'在'2009-11-10 10:00:00 -0700'之前，但是当插入DATETIME时，它们会以另一种方式排序。

您可以自行转换为 UTC。 在上面的示例中，您将
分别具有 '2009-11-10 16:00:00' 和 '2009-11-10 17:00:00'，这将正确排序。 检索数据时，您将使用时区信息将其还原为其原始形式。

我非常喜欢的一个建议是有三列：

• local_time DATETIME
• utc_time DATETIME
• time_zone VARCHAR(X) X 适合您存储在那里的数据类型的位置。 （我会选择64个字符作为时区/位置。

3 列方法的一个优点是它是显式的：使用单个DATETIME列，您无法一目了然地判断它是否在插入之前已转换为 UTC。

---

关于精度随时区/缩写/偏移量下降：

• 如果您有用户的时区/位置（例如 America/Juneau），则可以准确知道他们在过去或将来的任何时间点的挂钟时间（除非更改在该位置处理夏令时的方式）。 DST 的起点/终点以及是否使用它取决于位置，因此这是唯一可靠的方法。
• 如果您有时区缩写（如 MST（山区标准时间）或普通偏移量（如 -0700），您将无法预测过去或未来的挂钟时间。 例如，在美国，科罗拉多州和亚利桑那州都使用 MST，但亚利桑那州不遵守 DST。 因此，如果用户在冬季上传他的猫14:00 -0700照片，他是在亚利桑那州还是加利福尼亚州？ 如果您恰好在该日期中添加了六个月，那么对用户来说14:00还是13:00？

当应用程序将时间、日期或计划作为核心功能时，请考虑这些事项非常重要。

---

引用：

• MySQL 日期/时间参考
• 在MySQL
  中处理多个时区的正确方法 ^{（披露：我没有阅读整篇文章。}

Answer 3

MySQL存储DATETIME而不带时区信息。假设您将"2019-01-01 20：00：00"存储到 DATETIME 字段中，当您检索该值时，您应该知道它属于哪个时区。

因此，在您的情况下，当您将值存储到 DATETIME 字段中时，请确保它是坦桑尼亚时间。 然后当你把它拿出来时，就是坦桑尼亚时间了。耶！

现在，毛茸茸的问题是：当我进行插入/更新时，如何确保值是坦桑尼亚时间？ 两种情况：

1. 你做INSERT INTO table (dateCreated) VALUES (CURRENT_TIMESTAMP or NOW()).

2. INSERT INTO table (dateCreated) VALUES (?) ，并从应用程序代码中指定当前时间。

案例 #1

MySQL将采用当前时间，假设是坦桑尼亚时间'2019-01-01 20：00：00'。然后MySQL会将其转换为UTC，结果为"2019-01-01 17：00：00"，并将该值存储到字段中。

那么，如何让坦桑尼亚时间（即"20：00：00"）存储到现场呢？ 这是不可能的。 从此字段读取时，代码需要预期 UTC 时间。

案例 #2

这取决于您作为?传递的值类型。 如果您传递字符串 '2019-01-01 20：00：00'，那么对您有好处，这正是将存储到 DB 的内容。 如果您传递某种 Date 对象，那么这将取决于数据库驱动程序如何解释该 Date 对象，以及最终它提供给 MySQL 进行存储的"YYYY-MM-DD HH：mm：ss"字符串。 数据库驱动程序的文档应该会告诉您。

Answer 4

您描述的所有症状都表明您永远不会告诉MySQL使用哪个时区，因此它默认为系统的区域。想一想：如果它只有'2011-03-13 02:49:10'，它怎么能猜到这是坦桑尼亚当地的约会？

据我所知，MySQL没有提供任何语法来指定日期中的时区信息。您必须根据每个连接更改它;像这样：

SET time_zone = 'EAT';

如果这不起作用（要使用命名区域，您需要将服务器配置为这样做，但通常情况并非如此），您可以使用 UTC 偏移量，因为坦桑尼亚在撰写本文时不遵守夏令时，但当然这不是最佳选择：

SET time_zone = '+03:00';

<</div> div class="one_answers">

从MySQL 8.0.19开始，您可以在将TIMESTAMP值和DATETIME值插入表时指定时区偏移量。

偏移量追加到日期时间文本的时间部分，中间没有空格，并使用用于设置 time_zone 系统变量的相同格式，但以下情况除外：

• 对于小于 10 的小时值，需要前导零。
• 值"-00：00"被拒绝。
• 不能使用时区名称，例如"EET"和"Asia/Shanghai";"SYSTEM"也不能在此上下文中使用。

插入的值的月份部分、日期部分或两者部分不得为零。从MySQL 8.0.22开始强制执行，无论服务器SQL模式设置如何。

<小时 />

例：

此示例说明如何使用不同的time_zone设置将具有时区偏移量的日期时间值插入TIMESTAMP列和DATETIME列，然后检索它们：

mysql> CREATE TABLE ts (
    ->     id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->     col TIMESTAMP NOT NULL
    -> ) AUTO_INCREMENT = 1;
mysql> CREATE TABLE dt (
    ->     id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->     col DATETIME NOT NULL
    -> ) AUTO_INCREMENT = 1;
mysql> SET @@time_zone = 'SYSTEM';
mysql> INSERT INTO ts (col) VALUES ('2020-01-01 10:10:10'),
    ->     ('2020-01-01 10:10:10+05:30'), ('2020-01-01 10:10:10-08:00');
mysql> SET @@time_zone = '+00:00';
mysql> INSERT INTO ts (col) VALUES ('2020-01-01 10:10:10'),
    ->     ('2020-01-01 10:10:10+05:30'), ('2020-01-01 10:10:10-08:00');
mysql> SET @@time_zone = 'SYSTEM';
mysql> INSERT INTO dt (col) VALUES ('2020-01-01 10:10:10'),
    ->     ('2020-01-01 10:10:10+05:30'), ('2020-01-01 10:10:10-08:00');
mysql> SET @@time_zone = '+00:00';
mysql> INSERT INTO dt (col) VALUES ('2020-01-01 10:10:10'),
    ->     ('2020-01-01 10:10:10+05:30'), ('2020-01-01 10:10:10-08:00');
mysql> SET @@time_zone = 'SYSTEM';
mysql> SELECT @@system_time_zone;
+--------------------+
| @@system_time_zone |
+--------------------+
| EST                |
+--------------------+
mysql> SELECT col, UNIX_TIMESTAMP(col) FROM dt ORDER BY id;
+---------------------+---------------------+
| col                 | UNIX_TIMESTAMP(col) |
+---------------------+---------------------+
| 2020-01-01 10:10:10 |          1577891410 |
| 2019-12-31 23:40:10 |          1577853610 |
| 2020-01-01 13:10:10 |          1577902210 |
| 2020-01-01 10:10:10 |          1577891410 |
| 2020-01-01 04:40:10 |          1577871610 |
| 2020-01-01 18:10:10 |          1577920210 |
+---------------------+---------------------+
mysql> SELECT col, UNIX_TIMESTAMP(col) FROM ts ORDER BY id;
+---------------------+---------------------+
| col                 | UNIX_TIMESTAMP(col) |
+---------------------+---------------------+
| 2020-01-01 10:10:10 |          1577891410 |
| 2019-12-31 23:40:10 |          1577853610 |
| 2020-01-01 13:10:10 |          1577902210 |
| 2020-01-01 05:10:10 |          1577873410 |
| 2019-12-31 23:40:10 |          1577853610 |
| 2020-01-01 13:10:10 |          1577902210 |
+---------------------+---------------------+

注意：

• 遗憾的是，选择日期时间值时不显示偏移量，即使在插入日期时间值时使用也是如此。
• 支持的偏移值范围为 -13：59 到 +14：00（含）。
• 包含时区偏移量的日期时间文本被预准备语句接受为参数值。
• MySQL将TIMESTAMP值从当前时区转换为UTC以进行存储，并从UTC转换回当前时区以进行检索。（对于其他类型（如 DATETIME）不会发生这种情况。

Answer 5

你不能...你会发现很多答案说你"没有必要，使用UTC存储"，但它是：你需要用时区存储日期时间，而MySQL不能......

我在Postgres工作了10年，所有这些问题都不存在（日期时间和时区的管理没有摩擦，您可以透明地存储和比较不同时区表示的日期时间，ISOString格式自然管理，等等）。

我实际上在 MariaDB 工作，我不明白为什么在 2022 年，在全球化的世界中，MySQL 继续不支持按值时区。

Answer 6

我曾经也遇到过这样的问题，我需要保存不同协作者使用的数据，我最终以 unix 时间戳形式存储时间，该格式表示自 1970 年 1 月以来的秒数，这是一种整数格式。坦桑尼亚今天的日期和时间示例Friday, September 13, 2019 9:44:01 PM存储在 unix 时间戳中时将1568400241

现在，在读取数据时，只需使用 php 或任何其他语言，并从 unix 时间戳中提取日期。php 的一个例子将是

echo date('m/d/Y', 1568400241);

这使得甚至更容易与不同位置的其他协作者存储数据。他们可以简单地使用自己的 gmt 偏移量将日期转换为 unix 时间戳并将其存储为整数格式，当输出时，只需使用 a

Answer 7

相关内容

• 没有找到相关文章