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

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

  •  
  •   humbass · 74 天前 · 7137 次点击
    这是一个创建于 74 天前的主题,其中的信息可能已经有所发展或是发生改变。

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

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

    第 1 条附言  ·  74 天前

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

    感谢各位回帖。

    58 条回复    2022-09-26 18:27:41 +08:00
    Livid
        1
    Livid  
    MOD
       74 天前   ❤️ 5
    V2EX 的所有时间也是这么存的——Unix Timestamp

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

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

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

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

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

    关于我楼上说的,int(11),是建表之后查建表结构然后显示的那个是吗?楼主可以 Google 一下,这个 11 不影响存储的
    CEBBCAT
        23
    CEBBCAT  
       74 天前 via iPhone
    @CEBBCAT BIGINT 😄
    twinsdestiny
        24
    twinsdestiny  
       74 天前
    @CEBBCAT 你确定 int(11)能存下 13 位时间戳?不然楼主除 1000 干嘛
    humbass
        25
    humbass  
    OP
       74 天前
    @reter new 出来不会有负数,一般记录的时间肯定当前系统、用户生成的时间。
    chenqh
        26
    chenqh  
       74 天前
    存 0 时区的 datetime,mysql 类型 datetime,用 biginit,太难运维了
    triptipstop
        27
    triptipstop  
       74 天前
    小时候用 TP 存的都是时间戳,后来长大了用的 LV 存的就是 datetime ,这样看起来更优雅,用户面向全球,服务器设置成 0 时区,在客户端转换时区。
    dcsuibian
        28
    dcsuibian  
       74 天前
    周经贴,unsigned long ,存毫秒级时间戳
    JavaScript 里的数字都是 double ,但不超过 2^52 应该都是安全的
    fuchish112
        29
    fuchish112  
       74 天前
    datetime,方便查看
    timestamp,需要时区的话,用这种
    CEBBCAT
        30
    CEBBCAT  
       74 天前   ❤️ 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  
       74 天前
    最好存自己时区的数据到数据库,方便排查问题,其它时区只需要前端做一层转换就好了
    ericls
        32
    ericls  
       74 天前 via iPhone
    存未来的时间还是 string 好
    ladypxy
        33
    ladypxy  
       74 天前
    转换成 UTC Unix timestamp
    baobao1270
        34
    baobao1270  
       74 天前 via iPhone   ❤️ 1
    @Livid 时间戳有可能出现负数:比如存用户的生日,而用户生日可能在 1970 年之前。这些在设计表的时候都是要考虑的。
    james2013
        35
    james2013  
       74 天前 via Android
    那些说时区问题的,又不是国际软件?
    在本地,测试,正式服设置一次 mysql 配置,使用 datetime 就可以了
    Features
        36
    Features  
       74 天前
    我存俩,一个 datetime,一个 uint
    就是读取的时候免得转来转去的
    humbass
        37
    humbass  
    OP
       74 天前
    @CEBBCAT 多谢!

    一直不知道扩符号里头的 11 代表啥,我按实际需求的 int 位数写;
    那就是说:如果想存 unix time 在 int 里头,还是需要将 new Date().getTime() / 1000 , 是这个意思吗?
    humbass
        38
    humbass  
    OP
       74 天前
    @baobao1270 说到生日这个使用点了,确实有这个需求,还是不能直接使用 unsigned int @Livid
    codehz
        39
    codehz  
       74 天前   ❤️ 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  
       74 天前
    正经回答一下好了,存时间就别限制位数了,数据库也不差这么点,32 位时间戳很快就会撞到 2038 了(虽然数据库用十进制的倒是问题不大,但是这种莫名其妙的 11 的限制不会有啥好处)
    不过其实如果不是性能非常要紧的话,存 iso8601 就足够用了(数据库自带时间类型,可以自动转换的就忽略这点),主要除了程序处理,有时候还需要人工检查问题的,看时间戳不如看可读性好的文本
    CEBBCAT
        41
    CEBBCAT  
       74 天前
    @humbass 也许需要把时间戳获取和存储分开思考。说起来我现在才明白你说的除 1k 是什么意思,`Date().getTime()`获取的是毫秒时间戳,除以 1k 就变成了秒时间戳。

    关于怎么存,一两句话有点难以讲清楚,我推荐你配合 Google 计算一下 4 字节的 INT 能支持存储什么样的时间范围。
    2NUT
        42
    2NUT  
       74 天前
    @codehz #39 不能细想 坑很多
    Jooooooooo
        43
    Jooooooooo  
       74 天前
    用 int 存 unixtime 基本没啥问题.
    shiny
        44
    shiny  
       74 天前 via iPhone
    上次的润秒不知道有没有带来坑
    colatin
        45
    colatin  
       73 天前
    i18n 是老大难问题,是考验是否合格程序员的最低标准。
    iseki
        46
    iseki  
       73 天前   ❤️ 1
    @waising with time zone 并不是说数据库会存储输入的 timezone ,这是个比较迷惑的名字,存储的是一个绝对的时间点(实际上存的 UTC ),输入输出时按当前设定时区走
    iseki
        47
    iseki  
       73 天前
    @CEBBCAT BUGINT 可太真实了
    px920906
        48
    px920906  
       73 天前
    @codehz 除了最后一点,都不能理解... 求解释或者给些资料的链接,google 不过来😂
    leonshaw
        49
    leonshaw  
       73 天前
    @px920906 大概看出来有润秒、公元前后、历法变更、夏令时这几点。时间戳本身就是绝对时空观的产物,是时候建立相对论时间系统了。
    codehz
        50
    codehz  
       73 天前   ❤️ 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  
       73 天前
    时间戳能不能用 unsigned int 取决于你的业务是不是只需要处理 1970 以后的时间。如果真有生日之类的存储需求,考虑其他方式存储好了,没必要存到时间戳,只要存到日期好了,不需要精确到时分秒。
    twinsdestiny
        52
    twinsdestiny  
       73 天前
    @CEBBCAT 那你发个 insert 成功给我看看?
    mysql> insert into test values(1664076339000);
    ERROR 1264 (22003): Out of range value for column 'id' at row 1
    CEBBCAT
        53
    CEBBCAT  
       73 天前
    @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  
       73 天前
    datetime ,方便查看
    julyclyde
        55
    julyclyde  
       72 天前
    @wxf666 更新 tzdata 之后应该都能正确处理
    数据库软件的名声比个人的名声重要的多,他们肯定会努力维护的
    julyclyde
        56
    julyclyde  
       72 天前
    @reter 假设 1970 年之前是不存在的就可以了
    用计算机处理的数据大部分都适用于这种假设
    julyclyde
        57
    julyclyde  
       72 天前
    原则是:保存最原始的信息,展现个性化的信息
    aboat365
        58
    aboat365  
       72 天前
    如果你不需要国际化,那就简单很多,MySQL 建议直接使用 datetime 、date 、time 类型,人类可以直接读出。如果时间要国际化,那方案就复杂一些,但无非就是加入时区,成熟的开发语言都有对应的最佳实现。

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