首页 > 数据库 > MySQL

关于“时间”的一次探索

来源：SegmentFault

时间：2023-01-14 15:22:17 276浏览收藏

怎么入门数据库编程？需要学习哪些知识点？这是新手们刚接触编程时常见的问题；下面golang学习网就来给大家整理分享一些知识点，希望能够给初学者一些帮助。本篇文章就来介绍《关于“时间”的一次探索》，涉及到MySQL、javascript、sequelize，有需要的可以收藏一下

原文对 ISO 8601 时间格式中 T 和 Z 的表述有一些错误，我已经对原文进行了一些修订，抱歉给大家造成误解。

时间字符串

我们先说最复杂的时间字符串形式。它实际上支持两种格式：一种是 RFC-2822 的标准；另一种是 ISO 8601 的标准。我们主要介绍后一种。

ISO 8601

ISO 8601的标准格式是：

> new Date('1970-01-01 00:00:00')
Thu Jan 01 1970 00:00:00 GMT+0800 (CST)

> new Date('1970-01-01T00:00:00')
Thu Jan 01 1970 00:00:00 GMT+0800 (CST)

这里补充一点需要注意的，时间字符串这种形式有一个特殊的逻辑：如果你不提供“时间”（也就是

> new Date('1970-01-01')
Thu Jan 01 1970 08:00:00 GMT+0800 (CST)

> new Date('1970-01-01T00:00')
Thu Jan 01 1970 00:00:00 GMT+0800 (CST)

Z

> new Date('1970-01-01T00:00:00')
Thu Jan 01 1970 00:00:00 GMT+0800 (CST)

> new Date('1970-01-01T00:00:00Z')
Thu Jan 01 1970 08:00:00 GMT+0800 (CST)

示例 1 是东八区时间，显示的时间和传入的时间一致（因为我本地时区是东八区）。

示例 2 指定了 Z（也就是 UTC 零时区），显示的时间会加上本地时区的偏移（8 小时）。

RFC-2822

RFC-2822 的标准格式大概是这样：

> new Date('Thu Jan 01 1970 00:00:00 GMT+0800 (CST)')
Thu Jan 01 1970 00:00:00 GMT+0800 (CST)

除了能表示基本信息，还可以表示星期，但是一点也不容易读，不建议使用。完整的规范可以在这里查看：http://tools.ietf.org/html/rf...

时间戳

> new Date(1000 * 1)
Thu Jan 01 1970 08:00:01 GMT+0800 (CST)

传人 1 秒，等价于：

> new Date(1970, 0, 1, 0, 0, 0)
Thu Jan 01 1970 00:00:00 GMT+0800 (CST)

显示时间和传入时间一致，均是本地时间。注意：月份是从 0 开始的。

Date.parse

> Date.parse('1970-01-01 00:00:00')
-28800000

> new Date(Date.parse('1970-01-01 00:00:00'))
Thu Jan 01 1970 00:00:00 GMT+0800 (CST)

> Date.parse('1970-01-01T00:00:00')
0

> new Date(Date.parse('1970-01-01T00:00:00'))
Thu Jan 01 1970 08:00:00 GMT+0800 (CST)

示例 1，-28800000 换算后刚好是 8 小时表示的毫秒数，

> Date.UTC(1970,0,1,0,0,0)
0

> Date.parse('1970-01-01T00:00:00')
0

> Date.parse('1970-01-01 00:00:00Z')
0

可以看出，

> Date.now()
1452520484343

> new Date(Date.now())
Mon Jan 11 2016 21:54:55 GMT+0800 (CST)

> new Date()
Mon Jan 11 2016 21:55:00 GMT+0800 (CST)

MySQL 中的“时间”

MySQL 中和时间相关的数据类型主要包括：

CREATE TABLE `tests` (
    `id` INTEGER NOT NULL auto_increment , 
    `datetime` DATETIME, 
    `timestamp` TIMESTAMP, 
    PRIMARY KEY (`id`)
) ENGINE=InnoDB;

连接到数据库服务器后，可以执行

INSERT INTO `tests` (`id`, `datetime`, `timestamp`) VALUES (DEFAULT, '1970-01-01 00:00:00', '1970-01-01 00:00:00');

会发现有一个报错：

INSERT INTO `tests` (`id`, `datetime`, `timestamp`) VALUES (DEFAULT, '2000-01-01 00:00:00', '2000-01-01 00:00:00');

这次就成功插入了。

再次检索时结果也是正确的（数据库服务器将值从

SELECT * FROM sample.tests;

id	datetime	timestamp
1	2000-01-01 00:00:00	2000-01-01 00:00:00

如果我们先将

SET time_zone = '+00:00';
SELECT * FROM sample.tests;

id	datetime	timestamp
1	2000-01-01 00:00:00	1999-12-31 16:00:00

可以看到 datetime 列值没有受

connection.query("SET time_zone = '" + self.sequelize.options.timezone + "'"); /* jshint ignore: line */

第二个用途主要体现在两个地方：1）在 JavaScript 中调用 ORM 方法进行插入、更新时，需要将

SqlString.dateToString = function(date, timeZone, dialect) {
  if (moment.tz.zone(timeZone)) {
    date = moment(date).tz(timeZone);
  } else {
    date = moment(date).utcOffset(timeZone);
  }

  if (dialect === 'mysql' || dialect === 'mariadb') {
    return date.format('YYYY-MM-DD HH:mm:ss');
  } else {
    // ZZ here means current timezone, _not_ UTC
    return date.format('YYYY-MM-DD HH:mm:ss.SSS Z');
  }
};

代码逻辑如下：

检查
```
  switch (field.type) {
    case Types.TIMESTAMP:
    case Types.DATE:
    case Types.DATETIME:
    case Types.NEWDATE:
      var dateString = parser.parseLengthCodedString();
      if (dateStrings) {
        return dateString;
      }
      var dt;

      if (dateString === null) {
        return null;
      }

      var originalString = dateString;
      if (field.type === Types.DATE) {
        dateString += ' 00:00:00';
      }

      if (timeZone !== 'local') {
        dateString += ' ' + timeZone;
      }

      dt = new Date(dateString);
      if (isNaN(dt.getTime())) {
        return originalString;
      }

      return dt;
   // 更多代码...
}
```
处理过程大概是这样：
1. 用
```
Test.create({
    'datetime': new Date('2016-01-10 20:07:00'),
    'timestamp': new Date('2016-01-10 20:07:00')
  });
```
  会进行上面提到的 JavaScript 时间到 MySQL 时间字符串的转换，生成的 SQL 其实是（时间被转换为了
```
INSERT INTO `tests` (`id`,`datetime`,`timestamp`) VALUES (DEFAULT,'2016-01-10 12:07:00','2016-01-10 12:07:00');
```
  当我们执行
```
> new Date('2016-01-10 12:07:00+00:00')
Sun Jan 10 2016 20:07:00 GMT+0800 (CST)
```
  和我们插入时的时间是一致的。
  
  如果我们通过 MySQL 命令行来查询数据时，发现其实是这样的结果：
  
  id datetime timestamp
  1 2016-01-10 12:07:00 2016-01-10 20:07:00
  这很好理解，因为我们数据库服务器的
```
time_zone
```
  默认是东八区，
```
TIMESTAMP
```
  是受时区影响的，查询时被数据库服务器从
```
UTC
```
  时间转换回了
```
time_zone
```
  时区时间；
```
DATETIME
```
  不受影响，还是
```
UTC
```
  时间。
  如果我们先执行
```
SET time_zone = '+00:00'
```
  ，再进行查询，那结果就都会是
```
UTC
```
  时间了。所以，不要以为数据出错了哦。
  总结下就是，sequelize 会将本地时间转换为
```
UTC
```
  时间后入库，查询时再将
```
UTC
```
  时间转换为本地时间。这能达到最好的兼容性，存储总是使用
```
UTC
```
  时间，展示时应用端自己转换为本地时区时间后显示。当然这个的前提是数据类型选用
```
DATETIME
```
  。
  兼容老数据
  
  这里要说的最后一个问题是基于旧表定义 sequelize 模型，并且表中时间值插入时没有转换为
```
UTC
```
  时间（全部是东八区时间），而且
```
DATETIME
```
  和
```
TIMESTAMP
```
  混用，该怎么办？
  在默认配置下，情况如下：
  
  查询
```
DATETIME
```
  类型数据时，时间总是会晚 8 小时。比如，数据库中某条老数据的时间是
```
2012-01-01 01:00:00
```
  （已经是本地时间了，因为没转换），查询时被 sequelize 转换为
```
new Date('2012-01-01 01:00:00+00:00')
```
  ，显示时转换为本地时间
```
2012-01-01 09:00:00
```
  ，结果显然不对。
  查询
```
TIMESTAMP
```
  类型数据时，时间是正确的。这是因为
```
TIMESTAMP
```
  受
```
time_zone
```
  影响，sequelize 默认将其设置为
```
+00:00
```
  ，查询时数据库服务器先将时间转换到
```
time_zone
```
  设置的时区时间，由于没有时区偏移，刚好查出来的就是数据库中的值。比如：
```
2012-01-01 00:00:00
```
  （注意这个值是 UTC 时间），sequelize 将其转换为
```
new Date('2012-01-01 00:00:00+00:00')
```
  ，显示时转换为本地时间
```
2012-01-01 08:00:00
```
  ，刚好“侥幸”正确。
  新插入的数据 sequelize 会进行上一部分说的双向转换来保证结果的正确。
  
  维持默认配置显然导致查询
```
DATETIME
```
  不准确，解决方法就是将 sequelize 的
```
timezone
```
  配置为
```
+08:00
```
  。这样一来，情况变成下面这样：
  查询
```
DATETIME
```
  类型数据时，时间
```
2012-01-01 01:00:00
```
  被转换为
```
new Date('2012-01-01 01:00:00+08:00')
```
  ，显示时转换为本地时间
```
2012-01-01 01:00:00
```
  ，结果正确。
  查询
```
TIMESTAMP
```
  类型数据时，由于
```
time_zone
```
  被设置为了
```
+08:00
```
  ，数据库服务器先将库中
```
UTC
```
  时间
```
2011-01-01 00:00:00
```
  转换到
```
time_zone
```
  时区时间（加上 8 小时偏移）为
```
2011-01-01 08:00:00
```
  ，sequelize 将其转换为
```
new Date('2011-01-01 08:00:00+08:00')
```
  ，显示时转换为本地时间
```
2011-01-01 08:00:00
```
  ，结果正确。
  插入、更新数据时，所有 JavaScript 时间会转换为东八区时间入库。
  
  这样带来的问题是，所有入库时间都是东八区时间，如果有其他应用的时区不是东八区，那就需要自己基于东八区时间计算偏移并转换时间后显示了。
  
  参考资料
  
  一不小心写的有点长了，下面列出参考资料供大家进一步学习：
  以上就是本文的全部内容了，是否有顺利帮助你解决问题？若是能给你带来学习上的帮助，请大家多多支持golang学习网！更多关于数据库的相关知识，也可关注golang学习网公众号。