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)
其中:
`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
。