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

postgres 中的 timestamp without timezone 还真是有点反直觉

  •  
  •   zhwguest · 2023-09-26 12:12:45 +08:00 · 1965 次点击
    这是一个创建于 424 天前的主题,其中的信息可能已经有所发展或是发生改变。

    使用 Unix 时间戳的人(可以等价为 UTC),都喜欢用一个标准的时间来保存,读取后再根据时区来做显示,保存的时候也很方便。

    但是 Postgres 似乎没有提供 UTC 的时间戳保存方式,而是提供了一个 Timestamp without timezone ,这个东东写进去的时候还真是没有时区

    读取的时候才把该(保存)时间认为是你指定的时区,居然输出一个 UTC 时间。也就是说,把保存的时间当作浮动的(后面随便指定时区)。

    这刚好和我期望的是相反的,我希望保存的时间是具有固定(约定)时区的。

    真不知道这样的设计有什么使用场景。

    19 条回复    2023-10-06 03:25:26 +08:00
    liprais
        1
    liprais  
       2023-09-26 12:17:00 +08:00
    Timestamp without timezone 本来就该存 utc....
    lanlanye
        2
    lanlanye  
       2023-09-26 12:17:00 +08:00 via iPhone
    我猜是用在系统约定好保存时统一采用 UTC 的场景下,所有时区信息由应用自己处理。
    cxh116
        3
    cxh116  
       2023-09-26 12:24:39 +08:00 via Android   ❤️ 2
    那你应该使用 timestamp with time zone ,任意时区都会转成 UTC 来存储.

    TIMESTAMP '2004-10-19 10:23:54'
    is a timestamp without time zone, while

    TIMESTAMP '2004-10-19 10:23:54+02'
    is a timestamp with time zone.

    For timestamp with time zone, the internally stored value is always in UTC (Universal Coordinated Time, traditionally known as Greenwich Mean Time, GMT). An input value that has an explicit time zone specified is converted to UTC using the appropriate offset for that time zone. If no time zone is stated in the input string, then it is assumed to be in the time zone indicated by the system's TimeZone parameter, and is converted to UTC using the offset for the timezone zone.

    https://www.postgresql.org/docs/current/datatype-datetime.html (Edited)
    NessajCN
        4
    NessajCN  
       2023-09-26 12:32:25 +08:00
    啥叫「 Postgres 似乎没有提供 UTC 的时间戳保存方式」?
    一串 int64 的整数需要啥特殊保存方式吗?
    dayeye2006199
        5
    dayeye2006199  
       2023-09-26 12:59:13 +08:00
    postgres 默认时间的存储就是 unixtime
    zhwguest
        6
    zhwguest  
    OP
       2023-09-26 14:27:08 +08:00
    @cxh116 感谢,我就是奇怪 timestamp without zone 有什么实际用途。
    zhwguest
        7
    zhwguest  
    OP
       2023-09-26 14:28:15 +08:00
    各位说保存为 utc 的,那么怎么解释这个最后读取的结果是`2019-12-31 16:00:00+00`,这个+00 可不是我指定的。

    ```
    ```sql
    $ create table tabtstest(id serial primary key, tsnotz timestamp without time zone);
    CREATE TABLE

    $ insert into tabtstest(tsnotz) values('2020-01-01 0:0:0');
    INSERT 0 1

    $ select * from tabtstest;
    id | tsnotz
    ----+---------------------
    1 | 2020-01-01 00:00:00
    (1 row)

    $ select tsnotz AT TIME ZONE 'asia/shanghai' from tabtstest;
    timezone
    ------------------------
    2019-12-31 16:00:00+00
    ```
    zzzkkk
        8
    zzzkkk  
       2023-09-26 14:38:30 +08:00 via Android
    +00 表示没有显示东八区的时间 这个时间就是 0 区时间 你要自己做计算
    你把 at timezone 改成 America/new york 看看 结果是不是一样
    masterclock
        9
    masterclock  
       2023-09-26 14:47:58 +08:00   ❤️ 1
    PG 的 wiki 说了,不要用 timestamp without timezone

    timestamp without timezone 是 SQL 的规范,基本属于历史遗留问题
    cxh116
        10
    cxh116  
       2023-09-26 14:58:08 +08:00 via Android
    @zhwguest 存这种一开始就不带时区信息的时间

    TIMESTAMP '2004-10-19 10:23:54'
    julyclyde
        11
    julyclyde  
       2023-09-26 17:01:15 +08:00
    timestamp 这个词本来就是 UTC 啊
    多写一句“without timezone”都是废话吧?
    NoKey
        12
    NoKey  
       2023-09-26 19:59:16 +08:00
    存时间戳~😁
    zjp
        13
    zjp  
       2023-09-26 21:02:56 +08:00
    @julyclyde 那个是 Unix time ,中文下写 Unix timestamp 比较多,然后再简写成了 timestamp 。timestamp 原本应该不特指什么
    liuyunlong
        14
    liuyunlong  
       2023-09-27 09:12:30 +08:00
    如果能像 MySQL 名称为 datetime 的字段类型就好了,就解决这个问题了、
    zhwguest
        15
    zhwguest  
    OP
       2023-09-27 09:16:14 +08:00
    @zzzkkk 我是看见有人说保存的是 UTC 时间,我不同意这个说法。
    如果保存的是 UTC 时间,那么读取出来应该是`2020-01-01 0:0:0+0`或者`2019-12-31 16:00:00+08`。
    明显`AT TIME ZONE 'asia/shanghai'`就是告诉 pg ,这个保存的时间按照`asia/shanghai`来理解,而不是说按照 UTC 来理解。
    zhwguest
        16
    zhwguest  
    OP
       2023-09-27 09:16:54 +08:00
    @masterclock 嗯,历史遗留问题这个就好理解了。多谢。
    zhwguest
        17
    zhwguest  
    OP
       2023-09-27 09:17:43 +08:00
    我是保存的的不带时区啊:
    insert into tabtstest(tsnotz) values('2020-01-01 0:0:0');
    CRVV
        18
    CRVV  
       2023-09-27 16:12:37 +08:00 via iPhone
    https://en.m.wikipedia.org/wiki/Timestamp

    timestamp 指的是邮戳上那种日期时间。

    邮戳上当然不写时区,SQL 标准里面的 timestamp 类型也不带时区。如果你永远在一个不带夏令时的时区里面,就可以用这个类型。但数据库不知道这个时间是哪个时区的,你可以写在代码里面或者在系统上设置。单看这个时间戳本身,实际上不知道它具体是什么时间。

    如果给上面说的这种戳加上时区,比如
    2024-05-06 19:20:21 +10
    那么这个戳就是一个具体确定的时间,这个类型在 postgres 里面叫 timestamp with time zone
    postgres 只关心它具体是什么时间,你输入进去的时区不重要,也没有存。
    这个类型实际上就是 unix time ,并且在输入输出的时候帮你转换字符串。

    还有个 time with time zone 类型,文档里说了这个类型根本没用,也是 SQL 标准里面的。
    pger
        19
    pger  
       2023-10-06 03:25:26 +08:00
    timestamp with time zone 跟你认为的类型是一样的。
    可以参考下:
    https://www.rockdata.net/zh-cn/tutorial/type-timestamp/
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   1278 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 20ms · UTC 17:59 · PVG 01:59 · LAX 09:59 · JFK 12:59
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.