[mysql] 混乱的时区

Link: https://github.com/j2gg0s/j2gg0s/blob/main/20221116_mysql_%E6%B7%B7%E4%B9%B1%E7%9A%84%E6%97%B6%E5%8C%BA.md

MySQL 的时间类型, 无论是 TIMESTAMP 还是 DATETIME, 都是不带时区信息.

Binary Protocol 中对应的数据类型 MYSQL_TYPE_DATETIME 和 MYSQL_TYPE_TIMESTAMP 都没有任何字段来传递时区信息. go-sql-driver/mysql 中解析时间类型的 parseBinaryDateTime 和 parseDateTime 也没有从 server 返回的内容中解析出时间类型.

MySQL 将时区附加在链接上, 每个链接都有对应的时区, 在没有明确指定的情况下默认是 server 的时区.

链接的时区对 TIMESTAMP 和 DATETIME 的影响却又大不相同. 当插入或更新 TIMESTAMP 时, MySQL 会将更新的内容从链接的时区转换到 UTC 再存储. 当查询 TIMESTAMP 时, MySQL 会将读取的内容从 UTC 转换到链接的时区再展示. 而 DATETIME 的插入, 更新和查询却完全不受链接时区的影响.

go-sql-driver/mysql 类似的 driver 或者 orm, 一定程度上不区分 TIMESTAMP 和 DATETIME 又进一步加剧了混乱. 当然这不是 go-sql-driver/mysql 的问题, go-sql-driver/mysql 的实现质量还是非常又保证的.

上述的总总, 在写入和读取的代码不是同一套时, 格外的明显. examples/mysql-stamp 中构造了几个典型的例子验证理解.

mysql> SELECT * FROM visitor;
+----+--------+---------------------+---------------------+---------------------+
| id | name   | visited_timestamp   | visited_datetime    | created_at          |
+----+--------+---------------------+---------------------+---------------------+
|  1 | j2gg0s | 2022-11-16 22:17:00 | 2022-11-16 22:17:00 | 2022-11-16 14:18:29 |
+----+--------+---------------------+---------------------+---------------------+
1 row in set (0.01 sec)

mysql> SET @@session.time_zone='+08:00';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM visitor;
+----+--------+---------------------+---------------------+---------------------+
| id | name   | visited_timestamp   | visited_datetime    | created_at          |
+----+--------+---------------------+---------------------+---------------------+
|  1 | j2gg0s | 2022-11-17 06:17:00 | 2022-11-16 22:17:00 | 2022-11-16 22:18:29 |
+----+--------+---------------------+---------------------+---------------------+
1 row in set (0.00 sec)

func ExampleStamp() {
	for i, dsn := range []string{
		"root:root@tcp(127.0.0.1:3306)/j2gg0s?parseTime=true",
		"root:root@tcp(127.0.0.1:3306)/j2gg0s?parseTime=true&loc=Asia%2FShanghai",
		"root:root@tcp(127.0.0.1:3306)/j2gg0s?parseTime=true",
	} {
		db, err := sql.Open("mysql", dsn)
		if err != nil {
			panic(err)
		}
		if i == 2 {
			db.Exec("SET @@session.time_zone='+08:00'")
		}

		rows, err := db.Query("SELECT * FROM visitor")
		if err != nil {
			panic(err)
		}
		for rows.Next() {
			var id int64
			var name string
			var visitedTimeStamp, visitedDateTime time.Time
			var createdAt time.Time
			err := rows.Scan(&id, &name, &visitedTimeStamp, &visitedDateTime, &createdAt)
			if err != nil {
				panic(err)
			}
			fmt.Println(id, name, visitedTimeStamp.Format(time.RFC3339), visitedDateTime.Format(time.RFC3339))
		}
		db.Close()
	}
	// Output:
	// 1 j2gg0s 2022-11-16T22:17:00Z 2022-11-16T22:17:00Z
	// 1 j2gg0s 2022-11-16T22:17:00+08:00 2022-11-16T22:17:00+08:00
	// 1 j2gg0s 2022-11-17T06:17:00Z 2022-11-16T22:17:00Z
}

CRVV

2022-11-17 12:38:57 +08:00

如果已经注意到这种问题了，答案就是别用 MySQL ，这玩意遍地是坑。

@simonCN
@maggch97
op 说的问题不是怎么存时间，怎么用 timestamp.
他说的是 MySQL 的 timestamp ，不是通常说的 unix timestamp
(类似的还有 MySQL 的 utf8 也不是 UTF-8)

MySQL timestamp 的坑至少包含了：
1. 范围是 1970-2038 年
2. TIMESTAMP 直接把输入用本机的时区来理解（如果有冬夏令时，就不可能正常工作），MySQL 8.0.19 才能自己指定时区。
3. 默认自动更新，DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

这个类型根本就没考虑拿来存一个正常的时间，它的设计就是用来让你 ON UPDATE CURRENT_TIMESTAMP 的

DATETIME 的坑可能更多，一样很难把时区搞定。换个 SQLite 都没这么多坑