[mysql] 混乱的时区

[GopherDaily]

[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
}

反观 [PostgreSQL]() 在这方面就没有太重的妥协感, 增加了新的带时区的时间类型 [timestamptz],
允许使用者在传入时间的同时指定时区.

```bash
➜ pg-stamp git:(main) ✗ make init-database
PGPASSWORD=root psql -h 127.0.0.1 -U postgres -c "CREATE DATABASE j2gg0s"
CREATE DATABASE
➜ pg-stamp git:(main) ✗ make init-table
PGPASSWORD=root psql -h 127.0.0.1 -U postgres -d j2gg0s -c "CREATE TABLE visitor(id serial PRIMARY KEY, name VARCHAR(127) NOT NULL, visited_timestamp TIMESTAMP NOT NULL, visited_timestamptz TIMESTAMPTZ NOT NULL);"
CREATE TABLE
➜ pg-stamp git:(main) ✗ make insert
PGPASSWORD=root psql -h 127.0.0.1 -U postgres -d j2gg0s -c "INSERT INTO visitor(name, visited_timestamp, visited_timestamptz) VALUES('j2gg0s', '2022-11-17 11:30:00', '2022-11-17 11:30:00');"
INSERT 0 1
➜ pg-stamp git:(main) ✗ make insert-tz
PGPASSWORD=root psql -h 127.0.0.1 -U postgres -d j2gg0s -c "INSERT INTO visitor(name, visited_timestamp, visited_timestamptz) VALUES('j2gg0s', '2022-11-17 11:33:00+08', '2022-11-17 11:33:00+08');"
INSERT 0 1
➜ pg-stamp git:(main) ✗ make select
PGPASSWORD=root psql -h 127.0.0.1 -U postgres -d j2gg0s -c "SELECT * FROM visitor;"
id | name | visited_timestamp | visited_timestamptz
----+--------+---------------------+------------------------
1 | j2gg0s | 2022-11-17 11:30:00 | 2022-11-17 11:30:00+00
2 | j2gg0s | 2022-11-17 11:33:00 | 2022-11-17 03:33:00+00
(2 rows)
```

[simonCN]

TIMESTAMP 还能有时区信息？这个值不就是标准的是秒 /毫秒值么

[awanabe]

跨区就用 timestamp 展现的时候根据服务器的时区展现就行了

[thinkershare]

没啥好办法，单独用一个独立字段存储 timestamp 的实际时区好了。

[maggch97]

UTC 时间和 Unix timestamp 都不会有你这个问题，时区是展现时候才需要的，并且都是从 client 取得。先弄懂这几个概念再说吧

[GopherDaily]

@maggch97 mysql 支持在查询中传递 Unix Timestamp 吗？

[hsfzxjy]

入库一律转成 UTC 时间

[julyclyde]

timestamp 依法 UTC 啊

[springz]

时区统一存 Unix Timestamp ，或者 UTC 时间。前端去处理客户端时区。

[springz]

千万别自己发明，还会有其他国家夏令时等等一堆奇奇怪怪的问题。

[timethinker]

简单的理解一下：

时间：某一个时间点绝对值
时区：对时间点的修饰偏移

显然数据库存储的值不会根据你的时区是什么而发生变化，所以只能是在读取的时候根据当前已确定的时区进行不同的展示。

[springz]

时间这个问题是一个现实世界随时调整的一个变量，本质上是和计算机这个系统不兼容的，server 最好就是 Unix Timestamp 按需转现实时间，要不就是 UTC 。

[masterclock]

https://github.com/kdeldycke/awesome-falsehood#dates-and-time
时间处理非常复杂，基本方法是：
1. 通读上面的所以条目，去除脑子里的错误假设
2. 统一使用 UTC 直到最后给人看的时候
3. 用户输入的时间未必是某个特定时间

[CRVV]

如果已经注意到这种问题了，答案就是别用 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 都没这么多坑

[pengtdyd]

DATETIME 没有时区
TIMESTAMP 有 2038 问题

我个人觉的最好的解决方案是存 Bigint(20) UTC+0

[GopherDaily]

@CRVV MySQL 的 utf-8 是 utf8mb3 吧

[lisongeee]

时间戳为啥不直接存 long 值？

[CRVV]

@GopherDaily

utf8mb3 是 MySQL 自己造出来的词，utf8mb4 也是
UTF-8 这个东西，本来是最多 6 bytes 的变长编码，根本没有什么 mb3 mb4
后面觉得 6 bytes 没用，4 bytes 就足够了，就把标准改成了最长 4 bytes

https://en.wikipedia.org/wiki/UTF-8#History

[janxin]

你不在这里出问题，其他地方也肯定会出问题的。如果涉及到时区问题，最好的方案就是统一 UTC ，展示转换时区即可。

[GopherDaily]

@CRVV 嗯

我值，MySQL 中的 utf-8 其实是 utf8mb3 ；
实际的 utf-8 对应的是 utf8mb4;
在加上 connector/j 5.1.46 之前的 characterEncoding 不生效

这几个名词能把刚写代码的人玩哭

[unco020511]

存 timestamp,展示的时候转为当地时间

[qeqv]

以前做业务就觉得时区问题特别复杂。
比如把日期转为时间戳存储，有时候想知道一条数据到底是在哪个时区生成的，无法做到，因为时间戳里没有时区信息，如果想做到这一点得额外存储日期，有时候日期本身也没有带时区信息，就佷搞。

[optional]

远离 mysql ，不然整天纠结这些东西

[cheng6563]

存 varchar 完事

[swulling]

解决办法：

1. 不要使用 TIMESTAMP!!!!! 统一用 DATETIME
2. 如果要给 DATATIME 设置默认时间，用函数 UTC_TIMESTAMP() ，不要设置默认值 CURRENT_TIMESTAMP
3. 程序里写入到 DATATIME 中的时间，统一为 UTC ，读取时统一按照 UTC 处理。

[liprais]

没有时区不是应该存 utc 么

[julyclyde]

@CRVV mysql 的 timestamp 居然……