V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
JasonLaw
V2EX  ›  数据库

MySQL 时间“不正确”问题

  •  
  •   JasonLaw · 2020-08-22 20:00:59 +08:00 · 1785 次点击
    这是一个创建于 1314 天前的主题,其中的信息可能已经有所发展或是发生改变。

    准备工作(数据库)

    docker run --rm --name some-mysql -e MYSQL_ROOT_PASSWORD=my-secret-pw -e MYSQL_DATABASE=test -e TZ='Asia/Shanghai' -p 3306:3306 -d mysql:8
    
    docker exec -it some-mysql mysql -u root -pmy-secret-pw
    
    use test;
    create table t (dt datetime);
    

    真实结果 & 期望结果

    程序通过 MyBatis 执行insert into t (dt) values (#{now}),变量now是通过Instant.now()产生的,其值为2020-08-22T11:48:22.150Z,这是正确的。但是为什么数据库存储的是2020-08-22 06:48:22呢?不应该是2020-08-22 19:48:22吗?2020-08-22 06:48:22是怎么来的?

    16 条回复    2020-09-03 11:34:07 +08:00
    xupefei
        1
    xupefei  
       2020-08-22 20:09:18 +08:00 via iPhone
    你用的镜像有 tzdata 包么?
    mysql select now()看看结果?
    JasonLaw
        2
    JasonLaw  
    OP
       2020-08-22 20:20:08 +08:00
    `docker exec -it some-mysql mysql -u root -pmy-secret-pw`,然后执行`select now();`,其结果为`2020-08-22 20:19:13`。
    JasonLaw
        3
    JasonLaw  
    OP
       2020-08-22 20:20:20 +08:00
    @xupefei #1 `docker exec -it some-mysql mysql -u root -pmy-secret-pw`,然后执行`select now();`,其结果为`2020-08-22 20:19:13`。
    xupefei
        4
    xupefei  
       2020-08-22 20:29:48 +08:00 via iPhone
    把 now()插到表里在读出来,如果没问题的话就开始 debug mybatis 吧。
    xuanbg
        5
    xuanbg  
       2020-08-22 20:30:46 +08:00
    docker 容器的时区、mysql 的时区、jvm 的时区、tomcat 的时区,它们都不是一回事。要全统一为东八区,才能够让时间和现实保持一致。
    rockyou12
        6
    rockyou12  
       2020-08-22 20:44:57 +08:00
    首先你数据类型是啥? long 、timestamp 、datetime ?
    然后你工具不同也可能造成显示不一样(起码我之前用 navicat 和 idea 看 pg 的 timestampz,同个时间时区就是显示不一样),你 sql 里显式转成 utc 时间看是不是一致
    JasonLaw
        7
    JasonLaw  
    OP
       2020-08-22 21:06:34 +08:00
    @xupefei #4
    @xuanbg #5
    @rockyou12 #6

    通过在 connection URL string 中显式地设置时区( serverTimezone=Asia/Shanghai )解决了,原因是时区的不一致导致的,默认的时区应该是 UTC−05:00 。

    Setting the MySQL JDBC Timezone In Spring Boot | Baeldung - https://www.baeldung.com/mysql-jdbc-timezone-spring-boot#param
    chihiro2014
        8
    chihiro2014  
       2020-08-22 21:40:50 +08:00
    时区问题
    petelin
        9
    petelin  
       2020-08-23 03:37:39 +08:00 via iPhone
    所以 mysql 里面不要用时间 用一个 int64 不香吗
    JasonLaw
        10
    JasonLaw  
    OP
       2020-08-23 13:48:37 +08:00
    @petelin #9 关于使用什么数据类型存储时间,网上真的什么说法都有。下面是我找到的一些资料,希望对看到这个主题的人有所帮助。对于使用 BIGINT 存储时间,我暂时不太清楚那样子做的利弊,可能之后会尝试一下,看看它的利弊到底是什么。

    integer - Should I use a big INT or regular INT in MySQL to store a timestamp? - Stack Overflow - https://stackoverflow.com/questions/2031228/should-i-use-a-big-int-or-regular-int-in-mysql-to-store-a-timestamp

    mysql - DATETIME VS INT for storing time? - Stack Overflow - https://stackoverflow.com/questions/43705935/datetime-vs-int-for-storing-time

    Should I use the datetime or timestamp data type in MySQL? - Stack Overflow - https://stackoverflow.com/questions/409286/should-i-use-the-datetime-or-timestamp-data-type-in-mysql

    Adam D'Angelo's answer to What is the best way to store a timestamp in MySQL? - Quora - https://www.quora.com/What-is-the-best-way-to-store-a-timestamp-in-MySQL/answer/Adam-DAngelo
    m1ch3ng
        11
    m1ch3ng  
       2020-08-23 16:13:47 +08:00
    试试:echo "Asia/Shanghai" > /etc/timezone
    palfortime
        12
    palfortime  
       2020-08-23 20:27:44 +08:00 via Android
    @JasonLaw 不是默认时区是-05:00,是中国的时区缩写是 CST,美国 Central Standard Timezone 缩写也是 CST,mysql 默认认为 CST 是美国那个时区。
    JasonLaw
        13
    JasonLaw  
    OP
       2020-08-23 21:47:29 +08:00 via iPhone
    @palfortime 我不太明白你所说的“mysql 默认认为 CST 是美国那个时区”,我已经设置了时区为 Asia/Shanghai 了。
    palfortime
        14
    palfortime  
       2020-08-23 22:41:26 +08:00 via Android
    @JasonLaw 具体我也没有深入研究,我猜是服务器返回了 cst,mysql connector 就按美国 cst 时间来计算,传给服务器的时间就是西五区时间,也就是我国时间减十三小时。
    其实,我主要想表达你说的默认时区是西五区的说法是有问题的。一般来说有默认时区也应该是 GMT 。
    palfortime
        15
    palfortime  
       2020-08-24 11:38:58 +08:00   ❤️ 1
    详细看了一下:
    1. com.mysql.cj.protocol.a.NativeProtocol 的 configureTimezone 方法,会获取 mysql 服务器变量 system_time_zone,我的 mysql 中这个变量是 CST ;
    2. 接着,通过 TimeUtil.getCanonicalTimezone 这个方法,把 CST 转成唯一的时区表现格式;
    3. TimeUtil.getCanonicalTimezone 最终是从 sun.util.calendar.ZoneInfoFile 里获取到 CST 的映射:{ "CST", "America/Chicago" };

    所以,客户端误认为服务器使用 Chicago 的时间,上送时间时就会转换成对应的 Chicago 时间,但服务器实际是使用北京时间,所以导致服务器保存的时间与实际相差了 13 个小时。
    anUglyDog
        16
    anUglyDog  
       2020-09-03 11:34:07 +08:00
    @xuanbg 所以只需要系统对接系统只允许传递时间戳,根本不用考虑这些时区问题。
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   3088 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 36ms · UTC 10:57 · PVG 18:57 · LAX 03:57 · JFK 06:57
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.