Talk to Wallace

MySQL TIMESTAMP 类型不同时区引发的问题

先来看一段 JavaScript 代码：

const record = await query(`select * from data order by id desc limit 100`)
const flag = Date.now() - 1000 * 60 * 3 // 3 min ago
return record.filter(v => (new Date(v.ctime)).getTime() > flag)

其中：

这段代码连接的 mysql server 版本为5.7
query 是个封装了 github 上最流行的 mysql js client 的异步函数
id 是自增主键
ctime 是 data 表中一个类型为 TIMESTAMP 的列，准确来讲，它的定义是：

`ctime` timestamp NULL DEFAULT CURRENT_TIMESTAMP;

值得一提的是其实 mysql 官方（Oracle）也有个 mysql-connector-nodejs，但是 star 数只有一百多。。就很离谱😅

这段代码的意思是找到 data 表里最近 100 条数据里所有 ctime 在 3 分钟以内的记录，看上去没问题对吧？

但实际上如果运行环境的系统时区和 mysql 的时区设置不一样的话，就有大问题了。这里面涉及的东西有点多，而且比较绕，先来补充点背景知识。

TIMESTAMP 类型

根据 mysql 官方文档 https://dev.mysql.com/doc/refman/5.7/en/storage-requirements.html，存储 TIMESTAMP 类型的数据需要占用 4 bytes（以及可能的fractional seconds storage），无语的是文档里竟然没说是 4 字节的整数还是什么。。

不过 https://stackoverflow.com/a/1563551/3469145 有提到是 4 字节的整数，但是它里面的官方文档链接已经过期了，点进去会自动跳转到最新的8.0版文档。。

于是继续顺藤摸瓜，在这里 https://dev.mysql.com/doc/refman/5.7/en/datetime.html 找到了另一句话：

The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.

结合一下 4 字节整数的表示范围 (-2147483648) to (2147483647) 也就是

// 4 bytes = 32 bits, 再去掉一个符号位，就是31
console.log(2 ** 31) // 2147483648
console.log(new Date(1000 * (2147483648 - 1))) // Tue Jan 19 2038 11:14:07 GMT+0800 (中国标准时间)

所以我们可以得出结论了：MySQL 内部存储 TIMESTAMP 类型的数据用的是 4 字节整数，其数值等于从 UTC 时间 1970 年 1 月 1 日 0 点距今过去的秒数（一般称这个数值为时间戳，也就是 timestamp）。

那么问题来了，既然存的是秒数，那么时区什么的应该不影响啊，不管你在哪个时区，TIMESTAMP 表示的都是一个绝对时间，也就是说，你在不同时区的同一时刻，把 CURRENT_TIMESTAMP 存入 MySQL 数据库中，其内部存储的 4 字节整数理论上都是一致的。

那么时区到底是怎么发挥作用的呢？

MySQL 时区

在 MySQL 中，我们可以这样查看当前时区设置：

show variables like "%time_zone%";

也可以这样进行修改：

set time_zone = '+8:00';
set global time_zone = '+8:00';
flush privileges;

那么它是怎样影响 TIMESTAMP 类型的数据的呢？

根据官方文档 https://dev.mysql.com/doc/refman/5.7/en/datetime.html：

MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval.

也就是说，MySQL 在存储 TIMESTAMP 类型的数据时，会根据时区设置把它转成 4 位整数，而运行 select 语句时，会反过来根据存储的秒数和当前时区来算出表示时间的字符。

那么 MySQL 是怎么表示 TIMESTAMP 类型的呢？根据 https://dev.mysql.com/doc/refman/5.7/en/datetime.html:

A DATETIME or TIMESTAMP value can include a trailing fractional seconds part in up to microseconds (6 digits) precision. In particular, any fractional part in a value inserted into a DATETIME or TIMESTAMP column is stored rather than discarded. With the fractional part included, the format for these values is 'YYYY-MM-DD hh:mm:ss[.fraction]'

YYYY-MM-DD hh:mm:ss 也就是我们最常见的时间表示方法了，比如当前时间是：

dayjs().format('YYYY-MM-DD HH:mm:ss') // '2023-01-04 15:51:57'

这样表示时间的好处是非常直观，人类友好，坏处是它不是一个绝对时间，因为它丢失了时区信息，程序一般会根据运行环境所在时区来算出时间戳。

mysqljs/mysql 的处理

mysqljs/mysql 让这个问题藏得更深了一点，它对于 select 出来的原始数据，会再进行一次包装:

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

return dt;

这里面的 dateString，就是丢失了时区信息的 YYYY-MM-DD hh:mm:ss，JavaScript 运行 new Date(dateString) 的时候，就会根据当前系统的时区，来算出时间戳。

所以如果系统的时区和 MySQL 时区设置不一样，就会出问题了。我遇到的就是这种情况，我的系统时区是Asia/Shanghai，也就是 UTC+8，而 MySQL 是 UTC 时间。

举例来讲，如果某条数据的 ctime 为 2022-01-01 16:00:00（北京时间），那么它内部存储的时间戳就是 1641024000

new Date('Sat Jan 01 2022 16:00:00 GMT+0800 (中国标准时间)') / 1000 // 1641024000

而 select 出来的则是 2022-01-01 08:00:00（MySQL 用的是 UTC 时间，比北京时间少8个小时)

那么再运行到 new Date('2022-01-01 08:00:00') 时，JS 引擎用的是系统时区，也就是北京时间，就这么一来一去，ctime 平生生减去了 8 个小时。

顺便一提，如果数据库连接的配置中设置了 dateStrings: true，那么它就不会把 TIMESTAMP 包装成 Date 对象了，而是直接返回 dateString。