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
chenqh
V2EX  ›  MySQL

关于数据库时间字段的时区问题

  •  
  •   chenqh · 2020-01-30 21:20:07 +08:00 · 6400 次点击
    这是一个创建于 1759 天前的主题,其中的信息可能已经有所发展或是发生改变。

    大家数据库时区字段用的是 utc,还是本地,还是时间戳呢
    之前我都是用本地时间,但是曾经出过一次错,存了 utc 时间到数据库里面去了,导致我对本地时间有点怕怕的,而且存本地时间,好像需要更改服务器时区设置,好像是的
    但是使用 utc, 有点问题

    1. 渲染的时候需要加偏移(这只是麻烦点)
    2. 就是统计的问题了,如果是按天的统计表,那么这个统计表的day,那就是本地时间的了,这个样子有点郁闷呀 如果不使用统计表的方式,好像 sql 写起来会相当复杂呀!!
    28 条回复    2020-07-02 12:25:04 +08:00
    fuyufjh
        1
    fuyufjh  
       2020-01-30 21:36:01 +08:00   ❤️ 1
    大多数数据库都提供了两种列类型:自带时区和不带时区的时间戳,比如:
    MySQL:DATETIME (自带时区)和 TIMESTAMP (不带时区)
    PostgresQL:TIMESTAMP WITH TIME ZONE (自带时区)和 TIMESTAMP (不带时区)
    所谓自带时区,就是数据库不会帮你做额外的转换,你写入什么时间,读出来就是什么时间
    所谓不带时区,就是数据库实际上会保存 UTC 时间戳,写入的时候先按 Session 时区转成 UTC 时间,读出的时候再按 Session 时区转成当前时区的时间,这些转换都是透明的
    总结一下,你的这个需求,应该用不带时区的时间戳作为列类型,然后一切就搞定了
    chenqh
        2
    chenqh  
    OP
       2020-01-30 21:37:46 +08:00
    @fuyufjh 使用 utc 的时候,统计的逻辑会复杂很多呀
    fuyufjh
        3
    fuyufjh  
       2020-01-30 21:38:30 +08:00
    @chenqh “这些转换都是透明的”
    chenqh
        4
    chenqh  
    OP
       2020-01-30 21:45:40 +08:00
    @fuyufjh 没有搞懂, 假如我想统计这个月每天的订单数目,这个天肯定是我本地时间的天呀
    noqwerty
        5
    noqwerty  
       2020-01-30 21:56:01 +08:00 via Android
    @chenqh UTC 转本地时间还嫌麻烦吗老哥…
    Hellert
        6
    Hellert  
       2020-01-30 22:02:11 +08:00 via Android
    @fuyufjh 说反了吧?
    Mithril
        7
    Mithril  
       2020-01-30 22:12:26 +08:00
    @chenqh 如果你觉得以后会支持前端跑在不同时区,比如从国外访问,那你数据库就存 UTC。
    查询的时候让前台给你传 UTC 时间。或者只存个 offset。
    一般这些 ORM 都可以给你做了,配置一下就好了。
    如果你这个产品从头到尾都不会有人在不同时区使用,那就直接本地时间也没问题。
    chenqh
        8
    chenqh  
    OP
       2020-01-30 22:12:35 +08:00
    @noqwerty 只是一点点麻烦而已,关键是统计的时候
    suotm
        9
    suotm  
       2020-01-30 22:23:09 +08:00
    存成 unix 时间戳
    chenqh
        10
    chenqh  
    OP
       2020-01-30 23:28:27 +08:00
    @suotm 那统计表的按天统计怎么办? 使用时间戳或者 utc,居然会导致数据库里面存在两种时区?
    chenqh
        11
    chenqh  
    OP
       2020-01-30 23:34:34 +08:00
    突然发现一个问题,v2ex 的东 8 区时间,好像差了几分钟
    chenqh
        12
    chenqh  
    OP
       2020-01-30 23:36:40 +08:00
    @chenqh 看错了
    DonaldY
        13
    DonaldY  
       2020-01-31 03:35:26 +08:00
    @fuyufjh 这里的数据库隐式转换,包括驱动吗?
    keepeye
        14
    keepeye  
       2020-01-31 09:18:32 +08:00   ❤️ 1
    客户端连接数据库之后,设置本次连接的时区,这样 timestamp 字段读写就能自动转换了
    SET time_zone = timezone;
    optional
        15
    optional  
       2020-01-31 09:36:54 +08:00   ❤️ 1
    @chenqh 如果是 PG,设置客户端的连接时区, 所有 timestamp with time zone 都会自动按照设置的时区处理时间。
    chenqh
        16
    chenqh  
    OP
       2020-01-31 10:31:30 +08:00 via Android
    @keepeye 但是统计呢?
    bjking2014
        17
    bjking2014  
       2020-01-31 10:47:39 +08:00 via Android
    我们有澳洲,香港,英国的项目,都是用的 datetime 类型
    keepeye
        18
    keepeye  
       2020-01-31 11:03:34 +08:00
    @chenqh 不太明白你纠结的点在哪里,如果是不知道怎么聚合查询的话,可以这样写 where DATE(created_at) = 'xxxx-xx-xx'
    eason1874
        19
    eason1874  
       2020-01-31 11:11:19 +08:00
    我直接存 int 只由代码处理时区。
    ellermister
        20
    ellermister  
       2020-01-31 11:36:26 +08:00 via Android
    我也比较好奇这个问题,已经好久没有用时间类型了,一直是 int 存储。
    optional
        21
    optional  
       2020-01-31 11:47:02 +08:00
    @keepeye 他纠结的问题就在这里,如果 created_at 不带时区的话,不能按照 local date 的方式统计。
    keepeye
        22
    keepeye  
       2020-01-31 12:48:52 +08:00
    @optional 还是没明白,连接的时候指定下本地时区,查询的时候数据库会自动将 created_at 转换成本地时区
    keepeye
        23
    keepeye  
       2020-01-31 12:50:57 +08:00
    不信就做个试验吧,可能很多人还不理解 connection 可以设置时区

    mysql> select created_at from users limit 1;
    +---------------------+
    | created_at |
    +---------------------+
    | 2019-03-01 02:14:53 |
    +---------------------+
    1 row in set (0.00 sec)

    mysql> set time_zone='-8:00';
    Query OK, 0 rows affected (0.00 sec)

    mysql> select created_at from users limit 1;
    +---------------------+
    | created_at |
    +---------------------+
    | 2019-02-28 10:14:53 |
    +---------------------+
    1 row in set (0.00 sec)

    mysql> select date(created_at) from users limit 1;
    +------------------+
    | date(created_at) |
    +------------------+
    | 2019-02-28 |
    +------------------+
    1 row in set (0.00 sec)
    optional
        24
    optional  
       2020-01-31 12:58:07 +08:00
    @keepeye 我也是这个意思。
    chenqh
        25
    chenqh  
    OP
       2020-01-31 13:15:00 +08:00
    @keepeye 之前都不知知道 connection 可以设置时区,但是我想了一下,有一个问题,使用了带时区的 connection,insert 或者 update 的时候, 时间必须是本地时间?
    keepeye
        26
    keepeye  
       2020-01-31 13:21:45 +08:00
    @chenqh 对,mysql 会根据 connection 时区,将你本地写的值转换为 utc 存储,你只要设置好你本地和连接的时区就行了,数据转换对你来说是透明的
    chenqh
        27
    chenqh  
    OP
       2020-01-31 14:15:56 +08:00
    @keepeye 这个样子好复杂呀,假如一步出错,估计要查好久,算了,以后我还是用 timestamp 吧
    lialzm
        28
    lialzm  
       2020-07-02 12:25:04 +08:00
    @optional 麻烦问下 postgre 怎么在连接上设置时区我查到的都是修改 jvm 的参数
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   2523 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 26ms · UTC 15:35 · PVG 23:35 · LAX 07:35 · JFK 10:35
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.