V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
humbass
V2EX  ›  Node.js

比较好奇大家时间都是怎么存数据库的

  •  
  •   humbass · 2022-09-24 15:18:01 +08:00 · 9084 次点击
    这是一个创建于 551 天前的主题,其中的信息可能已经有所发展或是发生改变。

    目前我的项目都是直接使用 UTC 时间戳存,但是时间戳是 13 位的,所以除 1000 后,以 int(11) 的方式存

    主要是担心数据库有时区问题,造成时间有差异

    第 1 条附言  ·  2022-09-24 21:17:11 +08:00

    时间处理一直是一个头大的问题,本人是前端入手后端,后端也需要一把梭,因此在对于 Mysql 的掌握,仅仅到增删改、联合查询、改表等常规操作,无法深入做进一步优化,所以时间问题等于丢到 nodejs 层面来处理了。

    感谢各位回帖。

    58 条回复    2022-09-26 18:27:41 +08:00
    Livid
        1
    Livid  
    MOD
       2022-09-24 15:20:10 +08:00   ❤️ 6
    V2EX 的所有时间也是这么存的——Unix Timestamp

    而且因为时间戳不会出现负数,所以用的是 unsigned int
    JamesR
        2
    JamesR  
       2022-09-24 15:21:27 +08:00
    直接存整型。
    jybox
        3
    jybox  
       2022-09-24 15:25:06 +08:00
    大多数据库的时间类型,都是收到带时区的时间后,内部转换为不带时区的时间存储(一般是 UTC 时间戳),在客户端查询时再根据客户端的要求转换到指定的时区显示,所谓「时区问题」只是读写时数据库或客户端 library 没有正确配置。当然如果业务需要关注每一行数据的时区的话,可以单独用一个字段来存时区。
    dqzcwxb
        4
    dqzcwxb  
       2022-09-24 15:28:08 +08:00
    问个问题,数据库为什么要设计出 date 和 dateTime 数据类型?
    thinkershare
        5
    thinkershare  
       2022-09-24 15:36:11 +08:00
    @dqzcwxb 很多场景不需要 Time, 只需要日期,例如生日这种, 这样日期的有效范围可用很小, 然后是要更小的字节长度来存储。 而且一些语言中也有 Date 类型和 Time 不同的类型, 还有合起来的 DataTime 类型。
    wxf666
        6
    wxf666  
       2022-09-24 15:39:02 +08:00
    肯定存时间戳啊

    时区问题这么复杂,你确定数据库真的都能处理好了?

    万一以后中国又实行夏令时,你数据库咋处理。。
    go522000
        7
    go522000  
       2022-09-24 15:56:37 +08:00
    以前用 unsigned int ,后来新项目我一般都是用 datetime 类型。就单纯觉得在数据库工具中直接查看比较方便而已。
    Akitora
        8
    Akitora  
       2022-09-24 15:58:16 +08:00 via Android
    datetime ,方便查看
    zilongzixue
        9
    zilongzixue  
       2022-09-24 16:19:43 +08:00
    存 long 类型的时间戳,要用的时候加上时区
    iseki
        10
    iseki  
       2022-09-24 16:27:31 +08:00   ❤️ 1
    按 PostgreSQL FAQ 里的最佳实践,用 timestamp with time zone ,如果需要时区信息就单独开个字段存 IANA 时区数据库里的时区 ID
    Aloento
        11
    Aloento  
       2022-09-24 16:27:57 +08:00
    PG 自带时间类型,所以直接用内置 UTC 时间类型就行了
    nanmu42
        12
    nanmu42  
       2022-09-24 16:31:48 +08:00 via iPhone   ❤️ 4
    如果你用的是 PG ,我写过一篇文章,希望对你有帮助:

    学会和 PostgreSQL 的时间数据类型愉快玩耍
    https://nanmu.me/zh-cn/posts/2020/postgresql-date-and-time-data-types-explained/
    dswyzx
        13
    dswyzx  
       2022-09-24 16:38:26 +08:00
    mongodb 这样的不支持 datetime,mysql 之流则支持 datetime 类型存储字段.
    如果一个项目考虑跨时区,那么考虑时区转换问题,如果一个项目等到删库都没变过 utc+8:00,还是在 mysql 这种有 datetime 类型的数据库里,天天对着一长串时间戳排查 db 数据的时候那就只能开着个网页专门转换时间戳了.毕竟人脑识别 datetime 比 long 强
    leonshaw
        14
    leonshaw  
       2022-09-24 17:53:54 +08:00
    以前用 Hibernate 时候吃过时区的亏(有两个 CST ),后面时区信息都是另外存。
    waising
        15
    waising  
       2022-09-24 17:58:56 +08:00
    @iseki #10 数据都是同一个时区下是不是用 timestamp 更好
    moen
        16
    moen  
       2022-09-24 19:19:24 +08:00   ❤️ 1
    @waising 不好,without timezone 的情况下需要保证客户端输入的是 UTC ,否则容易出错。显然还是让 dbms 自己处理时区更能减少问题
    Cbdy
        17
    Cbdy  
       2022-09-24 19:22:34 +08:00
    时间戳或者 ISO8601
    myd
        18
    myd  
       2022-09-24 19:30:34 +08:00
    性能的瓶颈通常不在于 int 和 datetime ,所以我选择可读性更好的 datetime 。如果涉及到时区,就用时间戳。
    Jirajine
        19
    Jirajine  
       2022-09-24 19:35:09 +08:00   ❤️ 1
    大部分应用不需要也不应该处理、存储时区信息,用户发表了一个 post ,你只需要知道该 post 发布的“绝对时间”,没必要去记录用户是用什么时区发布的。

    “时区”只是作为时间信息输入 /输出 解析和格式化的时候的一项参数,与时间信息本身无关。就像存一个整数不需要关心它是 16 进制还是 10 进制、如何 padding 如何对齐,这些只是显示和解析时的参数。
    reter
        20
    reter  
       2022-09-24 19:48:09 +08:00   ❤️ 1
    #1 @Livid 说的并不正确,时间戳会出现负数,并且负数表示 1970 年 1 月 1 日之前的时间,所以不适合用无符号,应该用有符号。
    CEBBCAT
        21
    CEBBCAT  
       2022-09-24 20:10:57 +08:00 via iPhone
    20 层楼了,还没有人说 int(11) 的问题吗?
    CEBBCAT
        22
    CEBBCAT  
       2022-09-24 20:15:32 +08:00 via iPhone
    之前我也是像教科书上那样写的,存 datetime 。后来工作的公司在多个国家都有服务,所以学到了存 UNIX 时间戳的方式。MySQL 存的是 BUGINT ,无符号。

    关于我楼上说的,int(11),是建表之后查建表结构然后显示的那个是吗?楼主可以 Google 一下,这个 11 不影响存储的
    CEBBCAT
        23
    CEBBCAT  
       2022-09-24 20:16:07 +08:00 via iPhone
    @CEBBCAT BIGINT 😄
    twinsdestiny
        24
    twinsdestiny  
       2022-09-24 21:09:41 +08:00
    @CEBBCAT 你确定 int(11)能存下 13 位时间戳?不然楼主除 1000 干嘛
    humbass
        25
    humbass  
    OP
       2022-09-24 21:10:48 +08:00
    @reter new 出来不会有负数,一般记录的时间肯定当前系统、用户生成的时间。
    chenqh
        26
    chenqh  
       2022-09-24 21:25:47 +08:00
    存 0 时区的 datetime,mysql 类型 datetime,用 biginit,太难运维了
    triptipstop
        27
    triptipstop  
       2022-09-24 21:29:47 +08:00
    小时候用 TP 存的都是时间戳,后来长大了用的 LV 存的就是 datetime ,这样看起来更优雅,用户面向全球,服务器设置成 0 时区,在客户端转换时区。
    dcsuibian
        28
    dcsuibian  
       2022-09-24 21:43:21 +08:00
    周经贴,unsigned long ,存毫秒级时间戳
    JavaScript 里的数字都是 double ,但不超过 2^52 应该都是安全的
    fuchish112
        29
    fuchish112  
       2022-09-24 22:13:01 +08:00
    datetime,方便查看
    timestamp,需要时区的话,用这种
    CEBBCAT
        30
    CEBBCAT  
       2022-09-24 22:16:05 +08:00   ❤️ 2
    @twinsdestiny 我既然发出来了,那自然说明有一定自信。但为了防止误人子弟,我又去确认了一遍。int(11)中的 int 决定了存储空间,11 则是和 ZEROFILL 配合的,只影响显示。

    随附两个参考链接
    1. https://stackoverflow.com/q/5634104
    2. https://www.cnblogs.com/polk6/p/11595107.html

    我觉得不应该这么自大和冷漠
    ychost
        31
    ychost  
       2022-09-24 22:19:56 +08:00
    最好存自己时区的数据到数据库,方便排查问题,其它时区只需要前端做一层转换就好了
    ericls
        32
    ericls  
       2022-09-24 22:20:09 +08:00 via iPhone
    存未来的时间还是 string 好
    ladypxy
        33
    ladypxy  
       2022-09-24 22:27:52 +08:00
    转换成 UTC Unix timestamp
    baobao1270
        34
    baobao1270  
       2022-09-24 22:37:24 +08:00 via iPhone   ❤️ 1
    @Livid 时间戳有可能出现负数:比如存用户的生日,而用户生日可能在 1970 年之前。这些在设计表的时候都是要考虑的。
    james2013
        35
    james2013  
       2022-09-24 22:39:22 +08:00 via Android
    那些说时区问题的,又不是国际软件?
    在本地,测试,正式服设置一次 mysql 配置,使用 datetime 就可以了
    Features
        36
    Features  
       2022-09-24 22:43:18 +08:00
    我存俩,一个 datetime,一个 uint
    就是读取的时候免得转来转去的
    humbass
        37
    humbass  
    OP
       2022-09-24 22:55:02 +08:00
    @CEBBCAT 多谢!

    一直不知道扩符号里头的 11 代表啥,我按实际需求的 int 位数写;
    那就是说:如果想存 unix time 在 int 里头,还是需要将 new Date().getTime() / 1000 , 是这个意思吗?
    humbass
        38
    humbass  
    OP
       2022-09-24 22:59:06 +08:00
    @baobao1270 说到生日这个使用点了,确实有这个需求,还是不能直接使用 unsigned int @Livid
    codehz
        39
    codehz  
       2022-09-24 23:04:10 +08:00   ❤️ 1
    关于 unix 时间戳的几个错误认知:
    1. unix 时间戳表示从 UTC 1970 年 1 月 1 日 0 时 0 分到现在的秒数,错❌
    2. 等待一秒后 unix 时间戳+1 ,错❌
    3. 那么最起码,unix 时间戳不会递减,错❌
    关于时间格式的几个错误认知:
    1. 24:12:34 是一个非法的时间,错❌
    2. 每一个整数都是一个可能的年份,错❌
    3. 同一时间的年份在任何时间格式下都是一样的数字(如果有),错❌
    4. 每月的日数在 28 到 31 之间,错❌
    5. 星期六之前一定是星期五,错❌
    6. 时区之间的差异最短是 15 分钟,错❌
    7. 时区一定是到 UTC 到固定时间差异,错❌
    8. 下一个星期的同一时间一定是当前时间+7*864000 秒,错❌
    9. 你可以设计一个更好的时间系统,错❌
    codehz
        40
    codehz  
       2022-09-24 23:12:24 +08:00
    正经回答一下好了,存时间就别限制位数了,数据库也不差这么点,32 位时间戳很快就会撞到 2038 了(虽然数据库用十进制的倒是问题不大,但是这种莫名其妙的 11 的限制不会有啥好处)
    不过其实如果不是性能非常要紧的话,存 iso8601 就足够用了(数据库自带时间类型,可以自动转换的就忽略这点),主要除了程序处理,有时候还需要人工检查问题的,看时间戳不如看可读性好的文本
    CEBBCAT
        41
    CEBBCAT  
       2022-09-24 23:30:13 +08:00
    @humbass 也许需要把时间戳获取和存储分开思考。说起来我现在才明白你说的除 1k 是什么意思,`Date().getTime()`获取的是毫秒时间戳,除以 1k 就变成了秒时间戳。

    关于怎么存,一两句话有点难以讲清楚,我推荐你配合 Google 计算一下 4 字节的 INT 能支持存储什么样的时间范围。
    2NUT
        42
    2NUT  
       2022-09-24 23:34:25 +08:00
    @codehz #39 不能细想 坑很多
    Jooooooooo
        43
    Jooooooooo  
       2022-09-24 23:37:56 +08:00
    用 int 存 unixtime 基本没啥问题.
    shiny
        44
    shiny  
       2022-09-25 00:36:16 +08:00 via iPhone
    上次的润秒不知道有没有带来坑
    colatin
        45
    colatin  
       2022-09-25 01:33:21 +08:00
    i18n 是老大难问题,是考验是否合格程序员的最低标准。
    iseki
        46
    iseki  
       2022-09-25 01:40:02 +08:00   ❤️ 1
    @waising with time zone 并不是说数据库会存储输入的 timezone ,这是个比较迷惑的名字,存储的是一个绝对的时间点(实际上存的 UTC ),输入输出时按当前设定时区走
    iseki
        47
    iseki  
       2022-09-25 01:43:45 +08:00
    @CEBBCAT BUGINT 可太真实了
    px920906
        48
    px920906  
       2022-09-25 01:54:06 +08:00
    @codehz 除了最后一点,都不能理解... 求解释或者给些资料的链接,google 不过来😂
    leonshaw
        49
    leonshaw  
       2022-09-25 02:17:51 +08:00
    @px920906 大概看出来有润秒、公元前后、历法变更、夏令时这几点。时间戳本身就是绝对时空观的产物,是时候建立相对论时间系统了。
    codehz
        50
    codehz  
       2022-09-25 08:07:38 +08:00   ❤️ 1
    @px920906 时间戳的基本是关于闰秒的问题 https://alexwlchan.net/2019/05/falsehoods-programmers-believe-about-unix-time/
    然后 24 点是因为某些地区 dst 在 0 点调整
    年份的问题,0 不是一个有效的年份,公元一年的前一年是公元前一年
    年份不同格式不同则是因为有种格式是周日历,而这个格式下为了确保同一周是属于同一年,就会对年份数字进行调整,我记得某 java 格式化就会遇到这个问题
    月的长度: 1752 年 9 月历法变更
    星期的问题也源于此
    时区差异:印度在殖民地时期,三个时区分别为 GMT+4:51 GMT+5:21 和 GMT+5:54 (显然是人为故意设置的) https://en.wikipedia.org/wiki/UTC%2B04:51
    时区差异变化是因为不同国家的 DST 不同
    下一个星期的同一时间这个问题,除了历法变更这种不会再有(希望如此)的事情之外,主要还是闰秒,DST 的影响)
    remarrexxar
        51
    remarrexxar  
       2022-09-25 09:35:20 +08:00
    时间戳能不能用 unsigned int 取决于你的业务是不是只需要处理 1970 以后的时间。如果真有生日之类的存储需求,考虑其他方式存储好了,没必要存到时间戳,只要存到日期好了,不需要精确到时分秒。
    twinsdestiny
        52
    twinsdestiny  
       2022-09-25 11:30:20 +08:00
    @CEBBCAT 那你发个 insert 成功给我看看?
    mysql> insert into test values(1664076339000);
    ERROR 1264 (22003): Out of range value for column 'id' at row 1
    CEBBCAT
        53
    CEBBCAT  
       2022-09-25 15:08:36 +08:00
    @twinsdestiny 确实不能存。但不是一回事儿。我理解的楼主的逻辑是 int(11) 只能存 11 位长的数字,即 9,99999,99999 。所以我给楼主说,不是这么理解的。

    JS 获取时间戳返回的是以毫秒为单位的时间戳,但是实际上以秒为单位存就可以了。

    以现在时间 2022-09-25T14:53:31+08:00 为例,它的 UNIX 时间戳是 1664088811 。通过 https://www.rapidtables.com/convert/number/decimal-to-binary.html 换算得到二进制 (1100011001011111111101011101011)2 ,最高位是第 31 位(从 1 起),而 MySQL 带符号 INT——共 4 字节——可用空间是 31 位,恰好可以存储,而乘 1000 以后,大约多占了 10 个 bit 位,自然溢出。

    ---
    进一步计算得到,31 比特位存储的最大数字是 2147483647 。32 比特位则是 4294967295 。以 UNIX 时间戳记分别是 2038 年和 2106 年。
    xuanbg
        54
    xuanbg  
       2022-09-25 19:42:50 +08:00
    datetime ,方便查看
    julyclyde
        55
    julyclyde  
       2022-09-26 09:36:57 +08:00
    @wxf666 更新 tzdata 之后应该都能正确处理
    数据库软件的名声比个人的名声重要的多,他们肯定会努力维护的
    julyclyde
        56
    julyclyde  
       2022-09-26 09:37:55 +08:00
    @reter 假设 1970 年之前是不存在的就可以了
    用计算机处理的数据大部分都适用于这种假设
    julyclyde
        57
    julyclyde  
       2022-09-26 09:38:48 +08:00
    原则是:保存最原始的信息,展现个性化的信息
    aboat365
        58
    aboat365  
       2022-09-26 18:27:41 +08:00
    如果你不需要国际化,那就简单很多,MySQL 建议直接使用 datetime 、date 、time 类型,人类可以直接读出。如果时间要国际化,那方案就复杂一些,但无非就是加入时区,成熟的开发语言都有对应的最佳实现。

    顺便科普一下
    世界时:地球自转时间系统(地球自转速度不均匀)。
    协调世界时( UTC ):世界时因地球自转速度不均匀,引入闰秒,协调原子时和世界时而得出的一种时间系统。也是目前大家生活中使用的时间系统。
    原子时:原子共振頻率(目前最为精准)。
    Unix 时间:在计算机系统中,为了便于计算,规定每天 86400 秒,总秒数从协调世界时( UTC ) 1970 年 1 月 1 日 0 时 0 分 0 秒开始计算。
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   5614 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 34ms · UTC 01:42 · PVG 09:42 · LAX 18:42 · JFK 21:42
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.