CURRENT_TIME 在 MySQL 中插入行时时间戳不正确，我使用的是 AWS RDS

Question

edit：这是我使用node-mysql2 的mysql库的问题

我正在使用AWS RDS来托管MySQL数据库。

我的数据库中有一列具有以下定义

createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP

当我在美国东部时间下午4:43插入一行时，我得到了该列的以下值

2018-12-27T02:43:32.000Z

当我尝试将此值转换为EST时，我会得到

12/26/2018, 9:43:32 PM

这是不正确的。

我是在做一些不正确的事情，还是需要配置一些东西？

Answer 1

我觉得这个测试很有趣，虽然它不能回答你的问题，但它确实排除了节点库。

CREATE DATABASE IF NOT EXISTS test;
USE test;
CREATE TEMPORARY TABLE t (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
created DATETIME DEFAULT CURRENT_TIMESTAMP,
tz varchar(32)
);
select "datetime";
SET time_zone = "CST6CDT";
insert into t (tz) values (@@SESSION.time_zone);
SET time_zone = "EST5EDT";
insert into t (tz) values (@@SESSION.time_zone);
SET time_zone = "UTC";
insert into t (tz) values (@@SESSION.time_zone);
SET time_zone = "CST6CDT";
select *, @@SESSION.time_zone FROM t;
SET time_zone = "EST5EDT";
select *, @@SESSION.time_zone FROM t;
SET time_zone = "UTC";
select *, @@SESSION.time_zone FROM t;
DROP TEMPORARY TABLE t;
select "timestamp";
CREATE TEMPORARY TABLE t (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
tz varchar(32)
);
SET time_zone = "CST6CDT";
insert into t (tz) values (@@SESSION.time_zone);
SET time_zone = "EST5EDT";
insert into t (tz) values (@@SESSION.time_zone);
SET time_zone = "UTC";
insert into t (tz) values (@@SESSION.time_zone);
SET time_zone = "CST6CDT";
select *, @@SESSION.time_zone FROM t;
SET time_zone = "EST5EDT";
select *, @@SESSION.time_zone FROM t;
SET time_zone = "UTC";
select *, @@SESSION.time_zone FROM t;

在这里，我创建了一个带有DEFAULT CURRENT_TIMESTAMP列的临时表。我将会话变量设置为CST、EST和UTC中的每一个，以测试一些不同的本地时区，然后插入会话时区来跟踪哪个时区创建了哪个值。然后，我再次选择它们，将每个时区设置为我的会话时区，以显示插入会话的时区和选择会话的时区是如何交互的。

然后，我再次执行整个操作，与以前完全一样，但我第二次创建了带有TIMESTAMP字段的列，而不是DATETIME。

我在docker容器中运行它只是为了测试，我开始使用

docker run --rm -d -e MYSQL_ALLOW_EMPTY_PASSWORD=true --name mysql mysql

但是，从任何可以到达服务器的mysql客户端运行应该非常容易；这是我的docker exec调用：

$ docker exec -i mysql mysql -t  < t.sql
+----------+
| datetime |
+----------+
| datetime |
+----------+
+----+---------------------+---------+---------------------+
| id | created             | tz      | @@SESSION.time_zone |
+----+---------------------+---------+---------------------+
|  1 | 2018-12-27 11:07:05 | CST6CDT | CST6CDT             |
|  2 | 2018-12-27 12:07:05 | EST5EDT | CST6CDT             |
|  3 | 2018-12-27 17:07:05 | UTC     | CST6CDT             |
+----+---------------------+---------+---------------------+
+----+---------------------+---------+---------------------+
| id | created             | tz      | @@SESSION.time_zone |
+----+---------------------+---------+---------------------+
|  1 | 2018-12-27 11:07:05 | CST6CDT | EST5EDT             |
|  2 | 2018-12-27 12:07:05 | EST5EDT | EST5EDT             |
|  3 | 2018-12-27 17:07:05 | UTC     | EST5EDT             |
+----+---------------------+---------+---------------------+
+----+---------------------+---------+---------------------+
| id | created             | tz      | @@SESSION.time_zone |
+----+---------------------+---------+---------------------+
|  1 | 2018-12-27 11:07:05 | CST6CDT | UTC                 |
|  2 | 2018-12-27 12:07:05 | EST5EDT | UTC                 |
|  3 | 2018-12-27 17:07:05 | UTC     | UTC                 |
+----+---------------------+---------+---------------------+
+-----------+
| timestamp |
+-----------+
| timestamp |
+-----------+
+----+---------------------+---------+---------------------+
| id | created             | tz      | @@SESSION.time_zone |
+----+---------------------+---------+---------------------+
|  1 | 2018-12-27 11:07:05 | CST6CDT | CST6CDT             |
|  2 | 2018-12-27 11:07:05 | EST5EDT | CST6CDT             |
|  3 | 2018-12-27 11:07:05 | UTC     | CST6CDT             |
+----+---------------------+---------+---------------------+
+----+---------------------+---------+---------------------+
| id | created             | tz      | @@SESSION.time_zone |
+----+---------------------+---------+---------------------+
|  1 | 2018-12-27 12:07:05 | CST6CDT | EST5EDT             |
|  2 | 2018-12-27 12:07:05 | EST5EDT | EST5EDT             |
|  3 | 2018-12-27 12:07:05 | UTC     | EST5EDT             |
+----+---------------------+---------+---------------------+
+----+---------------------+---------+---------------------+
| id | created             | tz      | @@SESSION.time_zone |
+----+---------------------+---------+---------------------+
|  1 | 2018-12-27 17:07:05 | CST6CDT | UTC                 |
|  2 | 2018-12-27 17:07:05 | EST5EDT | UTC                 |
|  3 | 2018-12-27 17:07:05 | UTC     | UTC                 |
+----+---------------------+---------+---------------------+

正如您所看到的，DATETIME始终存储在会话的本地时间中。我不清楚如何知道是否将其转换为不同的时区，因为时区显然在内部存储为UTC，但显然不会针对SELECT sesson的时区进行更正。

另一方面，时间戳表现不同。在这些情况下，选择时，时间戳将正确转换为SELECT的会话时间。我认为这通常是数据库客户端所期望的行为(我设置了一个会话时区，所以显示该会话时区中的日期)。

简而言之，如果要存储本地时间，请使用datetime。如果您想存储"；绝对时间"；，使用时间戳。不确定这是否是一个好规则，但根据我的测试，这似乎是一个很好的方法。

当然，正如您所指出的，客户端确实仍然需要设置他们想要使用的时区。将所有内容设置为使用相同的时区可以避免整个问题：)

Answer 2

这不是AWS或MySQL 的问题

我发现了问题所在，node-mysql2决定转换时间戳。我不明白它在做什么转换，也不明白为什么。

我用来修复它的设置是

let options = {
...,
timezone: 'UTC', // Interpret all received timestamps as UTC. Otherwise local timezone is assumed.
dateStrings: [
'DATE', // DATE's are returned as strings (otherwise they would be interpreted as YYYY-MM-DD 00:00:00+00:00)
'DATETIME' // DATETIME's return as strings (otherwise they would interpreted as YYYY-MM-DD HH:mm:ss+00:00)
]
}

Answer 3

您不应该使用任何数据库日期、时间或时间戳格式。Oracle、MySQL和MS SQL Server；"优惠"；如本问题所述。当我们从变速箱工厂发货的变速箱在发货前到达装配厂时，我通过非常艰难的方式了解到了这一点。一家大型汽车公司的物流非常庞大，每天18轮车的流量超过100万英里。安排得好是个大问题，所以我们被告知现在就解决！

结果发现，操作系统认为它在一个时区，数据库确信它在另一个时区。当我们登录时，每个用户ID都被设置为不同的时区。

唯一的解决方案是将所有时间记录为GMT毫秒。这样，您可以检索间隔中的事件、比较时间、计算时间间隔，并合并来自多个源的数据。你必须担心以对每个用户都有意义的方式显示时间，但至少你不必担心底层计算的愚蠢。

永远不要，永远不要使用数据库时间戳——它们会以有趣的方式把你搞砸。