Mysql_混乱的时区
[mysql] 混乱的时区
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, 允许使用者在传入时间的同时指定时区.
➜ 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)