在Knex dateTime() / MySQL DATETIME字段中存储Node.js日期

Question

我在寻找一种在Node.js、knex和MySQL（通过Bookshelf）通用的日期格式。

我使用Knex schema builder设置了一个表：

knex.schema.createTableIfNotExists("examples", function (table) {
    ...
    table.dateTime("some_datetime");
})

这在MySQL中创建了一个具有DATETIME类型的列。

我有一个代表此列的书架模型 (此处省略所有样板文件)，并尝试使用内置的Date.now()作为值:

exampleModel.save({
    some_datetime: Date.now()
})

开启 Knex 的调试后，我发现该查询实际上是在尝试插入一个带有毫秒的 Epoch 时间戳（“…”为了简洁起见由我省略）：

{ ...
  bindings: [ 1485644012453, ... ],
  sql: 'update `examples` set `some_datetime` = ? where `id` = ?' }

但是这是不正确的，因为MySQL希望在这种情况下使用FROM_UNIXTIME，因此数据库中的结果日期当然是好老的0000-00-00 00:00:00。

在这里我应该做什么来使所有内容一致？

• 在创建表时，应该使用不同的类型吗？
• 或者，除了Date.now()之外，我应该使用其他东西来获取日期？
• 还是别的什么？

我很难找到共同点。我的直觉说在Knex中使用dateTime，在Node中使用Date.now()，在MySQL中使用DATETIME，但这是不正确的。

只是为了明确：这个问题并不一定集中于哲学上的正确性——目前我实际上无法弄清如何在数据库中存储日期/时间。我正在寻找一个可行的组合，语义上的正确性只是一个额外的奖励。

- Jason C

---

在模式生成器中创建表时，我应该使用不同的类型吗？您可以将列类型更改为足够大的整数。至于 Date.now() 部分，我无法确定，因为我不了解 X.js 方法。您正在使用的其他方法/脚本需要传递为 YYYY-MM-DD 00:00:00 以用于 DATETIME。 - Funk Forty Niner

@Fred-ii- 嗯，所以你建议完全放弃Knex和MySQL日期/时间类型，仅使用作为整数存储的时期时间，并根据需要转换为其他格式（例如UI）？那似乎有点不符合哲学思想，但这也是简单有效和合理的想法。我可能会这样做。谢谢。 - Jason C

这取决于你。不同的存储方法都有各自的优缺点。就个人而言，我喜欢使用MySQL内置的日期（时间）函数进行工作。之后，您将需要使用strtotime()或MySQL的DATE_FORMAT()来进行查询。 - Funk Forty Niner

Answer 1

JavaScript 函数 Date.now() 返回毫秒级的纪元时间。 Mysql 驱动程序，knex用于发送查询的操作，期望您将 ISO8601 字符串或 Date() 对象传递给 DATETIME 列。

日期对象将转换为 'YYYY-mm-dd HH:ii:ss' 格式的字符串。

https://github.com/mysqljs/mysql

所以，不要使用Date.now()，而是使用new Date()或new Date().toISOString() 编辑：

只是检查mysql是否真的接受.toISOString()输出，因为我在文档https://dev.mysql.com/doc/refman/5.7/en/date-and-time-type-overview.html中找不到相关说明。

MariaDB [(none)]> select CAST('2017-01-30T16:49:19.278Z' AS DATETIME);
+----------------------------------------------+
| CAST('2017-01-30T16:49:19.278Z' AS DATETIME) |
+----------------------------------------------+
| 2017-01-30 16:49:19                          |
+----------------------------------------------+
1 row in set, 1 warning (0.00 sec)

看起来是真的。耶！一切都好。

编辑2：实际上，仅使用.toISOString()会导致某些情况下出现警告和错误，因此插入UTC DATETIME列时应该使用.toISOString().replace('Z','').replace('T', ' ')或任何其他首选方法将UTC datetime对象转换为格式yyyy-MM-dd HH:mm:ss。

另外，如果您正在使用TIMESTAMP列类型，并且想要将UTC时间插入数据库，则重要的是还将数据库会话设置为UTC。否则，javascript时间戳将被解释为本地数据库时间，并在存储之前从其转换为UTC。

CREATE TABLE test (descr TEXT, dt DATETIME, ts TIMESTAMP);

SET time_zone = '+8:00';
INSERT INTO test (descr, dt, ts) VALUES 
  ('session tz +08:00 insert local times', '2020-01-01T00:00:00', '2020-01-01T00:00:00');

SET time_zone = '+0:00';
INSERT INTO test (descr, dt, ts) VALUES 
  ('session tz +00:00 insert local times', '2020-01-01 00:00:00', '2020-01-01 00:00:00');


SET time_zone = '+02:00';
select 'server local time:', now();
select * from test;

SET time_zone = '+08:00';
select 'server local time:', now();
select * from test;

---

**Query #1**

    SET time_zone = '+02:00';

There are no results to be displayed.

---
**Query #2**

    select 'server local time:', now();

| server local time: | now()               |
| ------------------ | ------------------- |
| server local time: | 2020-05-10 16:38:26 |

---
**Query #3**

    select * from test;

| descr                                | dt                  | ts                  |
| ------------------------------------ | ------------------- | ------------------- |
| session tz +08:00 insert local times | 2020-01-01 00:00:00 | 2019-12-31 18:00:00 |
| session tz +00:00 insert local times | 2020-01-01 00:00:00 | 2020-01-01 02:00:00 |

---
**Query #4**

    SET time_zone = '+08:00';

There are no results to be displayed.

---
**Query #5**

    select 'server local time:', now();

| server local time: | now()               |
| ------------------ | ------------------- |
| server local time: | 2020-05-10 22:38:26 |

---
**Query #6**

    select * from test;

| descr                                | dt                  | ts                  |
| ------------------------------------ | ------------------- | ------------------- |
| session tz +08:00 insert local times | 2020-01-01 00:00:00 | 2020-01-01 00:00:00 |
| session tz +00:00 insert local times | 2020-01-01 00:00:00 | 2020-01-01 08:00:00 |

---

在DB Fiddle上查看

展示了存储和读取时间戳实际上总是作为本地时间处理，而DATETIME与数据库会话的时区设置无关。

通过快速测试，我甚至无法使yyyy-MM-dd HH:mm:ss+zz:zz类型的时间戳/日期时间在mysql 8上工作。

- Mikael Lepistö

---

“1个警告”实际上是一个问题，当使用sql_mode STRICT_TRANS_TABLES或STRICT_ALL_TABLES写入ISO日期时。MySQL不支持ISO日期；它只在您的示例中似乎工作，因为T被明确允许作为分隔符而不是空格。时区部分（这里的“Z”）不兼容。请参见：https://dev.mysql.com/doc/refman/5.7/en/date-and-time-literals.html - jelder

感谢提供的信息！为了将正确的时间存储到数据库中，应该首先将Date()对象解释为数据库时区的本地时间，然后再将其作为没有时区的时间戳存储吗？或者有办法在时间戳字符串中告知时区偏移量吗？ - Mikael Lepistö

据我所知，MySQL不支持在任何日期列中存储时区。它在存储时被转换为UTC，当检索时再转换回本地时间。 - jelder

真实的时区信息并没有存储到数据库中，但问题是当没有时区信息的时间戳被传递给数据库时，MySQL如何解释它。我的意思是2017-01-30T16:49:19.278Z很清楚地表示时间是以UTC时区给出的。即使DB时区已经设置为其他地方，该时间戳仍然是绝对的，并且不依赖于DB的本地时区。但是，当2017-01-30T16:49:19.278被传递给MySQL时，时间戳会被解释为数据库的本地时区还是UTC时区（也许直接尝试会更容易）。 - Mikael Lepistö

好的，看起来从mysql 8.0开始支持绝对时间类型，例如+00:00等，而不必插入本地时间，这些时间将被转换为UTC并存储为DATETIME列类型 https://dev.mysql.com/doc/refman/8.0/en/datetime.html 我很快会用更好的例子更新答案。 - Mikael Lepistö

Answer 2

你可以使用Knex.js内置的now()函数来替代Date.now()：

const knexfile = require('../../knexfile');
const knex = require('knex')(knexfile.development);
const date = knex.fn.now();

替代方案：

const knexfile = require('../../knexfile');
const knex = require('knex')(knexfile.development);
knex.raw('CURRENT_TIMESTAMP');

- Benny Code

---

无论是knex.fn.now()还是knex.raw，它们都只返回一个原始对象，我无法将其转换为日期。 - Ryker