学习笔记 - 关于多个时区 mysql 查询的个人经验

背景

由于多个国家时区不同，当按照日期查询时，不同的国家需要查询出对应时区的结果，这里记录下实现过程中遇到的问题。

问题

数据库结构

• 关于时间的部分，各人建议不要使用 datetime，原因很简单，这个格式不带时区，在跨时区场景下基本上是一定有问题的，虽然 timestamp 存在 2038 年问题。

日期比较

常用的日期比较一般是这样的：

SELECT * FROM `example_table` WHERE createdAt >= '2006-01-02 00:00:00' AND createdAt <= '2006-01-03 00:00:00';

由于数据库会根据当前数据库的时间算日期，只要客户端的当前时区和数据库的时区有差异，那用这个 sql 就一定有问题。

这里提几个点：

1. 不能用带时区的字符串来做这件事，类似这样 2024-12-10T17:46:06+08:00 数据库会警告，因为数据库并不能通过这种 format 做到用对应时区来查询。
2. 可以通过函数 UNIX_TIMESTAMP() 来将数据库时间和入参时间都转成 unix 时间戳来做这件事，但不能直接使用。因为如果直接转换，假设数据库是东八区的时区，而入参是 UTC 时区的时间，那么用转换出的值做比较仍然是错的，必须先统一时区，然后在进行时间戳转换，才能保证一致性。

方案

我的做法是，先获取 go 语言的时间类型（入参是当地时间 0 点的 UTC 时间），加 24 小时作为区间边界，然后再用 sql 做转换比较。

dateTime, err := time.Parse(time.RFC3339, date)
if err != nil {
    return nil, err
}
err = db.Table("example_table").
	Where(`UNIX_TIMESTAMP(CONVERT_TZ(startedAt, 'Asia/Shanghai', '+00:00')) >= ?`, dateTime.Unix()).
	Where(`UNIX_TIMESTAMP(CONVERT_TZ(startedAt, 'Asia/Shanghai', '+00:00')) <= ?`, dateTime.Add(time.Hour*24).Unix()).
	Order(`startedAt ASC`).Find(&docs).Error
if err != nil {
	return nil, err
}