V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
MySQL 5.5 Community Server
MySQL 5.6 Community Server
Percona Configuration Wizard
XtraBackup 搭建主从复制
Great Sites on MySQL
Percona
MySQL Performance Blog
Severalnines
推荐管理工具
Sequel Pro
phpMyAdmin
推荐书目
MySQL Cookbook
MySQL 相关项目
MariaDB
Drizzle
参考文档
http://mysql-python.sourceforge.net/MySQLdb.html
GopherDaily
V2EX  ›  MySQL

[mysql] 混乱的时区

  •  
  •   GopherDaily · 2022-11-17 10:06:48 +08:00 · 3143 次点击
    这是一个创建于 497 天前的主题,其中的信息可能已经有所发展或是发生改变。

    [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_DATETIMEMYSQL_TYPE_TIMESTAMP 都没有任何字段来传递时区信息. go-sql-driver/mysql 中解析时间类型的 parseBinaryDateTimeparseDateTime 也没有从 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
    }
    
    第 1 条附言  ·  2022-11-17 11:53:21 +08:00
    反观 [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)
    ```
    26 条回复    2022-11-18 08:52:35 +08:00
    simonCN
        1
    simonCN  
       2022-11-17 10:23:42 +08:00   ❤️ 1
    TIMESTAMP 还能有时区信息?这个值不就是标准的是秒 /毫秒值么
    awanabe
        2
    awanabe  
       2022-11-17 10:26:14 +08:00
    跨区就用 timestamp 展现的时候根据服务器的时区展现就行了
    thinkershare
        3
    thinkershare  
       2022-11-17 10:31:48 +08:00
    没啥好办法,单独用一个独立字段存储 timestamp 的实际时区好了。
    maggch97
        4
    maggch97  
       2022-11-17 10:34:41 +08:00   ❤️ 3
    UTC 时间和 Unix timestamp 都不会有你这个问题,时区是展现时候才需要的,并且都是从 client 取得。先弄懂这几个概念再说吧
    GopherDaily
        5
    GopherDaily  
    OP
       2022-11-17 10:51:22 +08:00
    @maggch97 mysql 支持在查询中传递 Unix Timestamp 吗?
    hsfzxjy
        6
    hsfzxjy  
       2022-11-17 11:07:32 +08:00 via Android
    入库一律转成 UTC 时间
    julyclyde
        7
    julyclyde  
       2022-11-17 11:11:47 +08:00
    timestamp 依法 UTC 啊
    springz
        8
    springz  
       2022-11-17 11:12:36 +08:00
    时区统一存 Unix Timestamp ,或者 UTC 时间。前端去处理客户端时区。
    springz
        9
    springz  
       2022-11-17 11:13:53 +08:00
    千万别自己发明,还会有其他国家夏令时等等一堆奇奇怪怪的问题。
    timethinker
        10
    timethinker  
       2022-11-17 11:13:59 +08:00
    简单的理解一下:

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

    显然数据库存储的值不会根据你的时区是什么而发生变化,所以只能是在读取的时候根据当前已确定的时区进行不同的展示。
    springz
        11
    springz  
       2022-11-17 11:16:11 +08:00
    时间这个问题是一个现实世界随时调整的一个变量,本质上是和计算机这个系统不兼容的,server 最好就是 Unix Timestamp 按需转现实时间,要不就是 UTC 。
    masterclock
        12
    masterclock  
       2022-11-17 11:22:12 +08:00
    https://github.com/kdeldycke/awesome-falsehood#dates-and-time
    时间处理非常复杂,基本方法是:
    1. 通读上面的所以条目,去除脑子里的错误假设
    2. 统一使用 UTC 直到最后给人看的时候
    3. 用户输入的时间未必是某个特定时间
    CRVV
        13
    CRVV  
       2022-11-17 12:38:57 +08:00   ❤️ 2
    如果已经注意到这种问题了,答案就是别用 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
        14
    pengtdyd  
       2022-11-17 12:49:34 +08:00
    DATETIME 没有时区
    TIMESTAMP 有 2038 问题

    我个人觉的最好的解决方案是存 Bigint(20) UTC+0
    GopherDaily
        15
    GopherDaily  
    OP
       2022-11-17 13:22:16 +08:00
    @CRVV MySQL 的 utf-8 是 utf8mb3 吧
    lisongeee
        16
    lisongeee  
       2022-11-17 13:48:46 +08:00
    时间戳为啥不直接存 long 值?
    CRVV
        17
    CRVV  
       2022-11-17 13:52:43 +08:00
    @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
        18
    janxin  
       2022-11-17 14:08:58 +08:00
    你不在这里出问题,其他地方也肯定会出问题的。如果涉及到时区问题,最好的方案就是统一 UTC ,展示转换时区即可。
    GopherDaily
        19
    GopherDaily  
    OP
       2022-11-17 14:11:15 +08:00
    @CRVV

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

    这几个名词能把刚写代码的人玩哭
    unco020511
        20
    unco020511  
       2022-11-17 15:11:54 +08:00
    存 timestamp,展示的时候转为当地时间
    qeqv
        21
    qeqv  
       2022-11-17 17:01:27 +08:00
    以前做业务就觉得时区问题特别复杂。
    比如把日期转为时间戳存储,有时候想知道一条数据到底是在哪个时区生成的,无法做到,因为时间戳里没有时区信息,如果想做到这一点得额外存储日期,有时候日期本身也没有带时区信息,就佷搞。
    optional
        22
    optional  
       2022-11-17 17:11:06 +08:00 via iPhone
    远离 mysql ,不然整天纠结这些东西
    cheng6563
        23
    cheng6563  
       2022-11-17 17:11:13 +08:00
    存 varchar 完事
    swulling
        24
    swulling  
       2022-11-17 17:21:19 +08:00
    解决办法:

    1. 不要使用 TIMESTAMP!!!!! 统一用 DATETIME
    2. 如果要给 DATATIME 设置默认时间,用函数 UTC_TIMESTAMP() ,不要设置默认值 CURRENT_TIMESTAMP
    3. 程序里写入到 DATATIME 中的时间,统一为 UTC ,读取时统一按照 UTC 处理。
    liprais
        25
    liprais  
       2022-11-17 17:23:32 +08:00
    没有时区不是应该存 utc 么
    julyclyde
        26
    julyclyde  
       2022-11-18 08:52:35 +08:00
    @CRVV mysql 的 timestamp 居然……
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   1964 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 29ms · UTC 16:22 · PVG 00:22 · LAX 09:22 · JFK 12:22
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.