李守中
该站已迁往根域名 https://lishouzhong.com
需要注意,迁移后的文章的 url 可能会发生变化。
域名 https://note.lishouzhong.com 下的内容将不再更新,但已有内容会永久保留。

PostgreSQL 中的时间

Table of Contents

PG 提供了多个用于描述时间的数据类型:

关于什么时候不应该使用什么数据类型,可以看这篇 PostgreSQL Don't Do This 中文译本

1. 时间戳

PG 按照 SQL 标准提供了两个时间戳类型:

  • timestamp (timestamp without time zone) 不带时区
  • timestamptz (timestamp with time zone) 带时区

多数情况下,应使用 timestamptz;不应将时间转换为 UTC 再存储到 timestamp 中。 PG 官方也 推荐这样做

有一个误解: timestamptz 要存储时区,所以它比 timestamp 使用了更多存储空间。而实际上,timestamp 和 timestamptz 在 PG 都只占 8 字节,并且它们有相同的表示范围 (4713 BC - 294276 AD)、相同的分辨率(1 毫秒)。

PG 在存储 timestamptz 格式的数据时,会自动将时间转换为 UTC 时间再存储,也就是说,timestamptz 实际上存储的就是 UTC 时间。客户端查询时间时,数据库会自动把查到的时间转到客户端使用的时区再发给客户端。而 timestamp 完全无视时区,需要客户端自己来造轮子处理。

不让业务代码先把时间转 UTC 再存到 timestamp 的原因是:

  • 处理时间相关的逻辑需要写更多的代码、有更多的负担,才能实现一个数据库已经提供的功能
  • PG 把时区信息和夏令时信息处理得非常好 (select * from pg_timezone_names;),自己造的轮子不一定比 PG 自带的好用
  • timestamptz 能够让 date_trunc()generate_series() 等函数在相应时区下正常工作

但是,有些情况下 timestamptz 也没那么好用:

  • 现有业务严重依赖 timestamp,引入 timestamptz 会导致相当多额外的工作
  • 非常关注未来的本地时间,而且用户所处时区有可能经常变化,这些情况下 PG 的轮子也帮不上忙
    • 比如日历应用的提醒功能: 需要在本地时区的 7:00 am 推送消息。这时候不带时间戳的 timestamp 更好用
-- 设置当前会话的时区
-- 会话的时区也可以在数据库连接字符串 DSN 中指定,比如 timezone=Asia/Shanghai
set time zone "Asia/Shanghai";

-- 显示当前会话的时区
show timezone;
--    TimeZone
-- ---------------
--  Asia/Shanghai
-- (1 row)

-- 将字符串解析为带时区的时间,如果没有提供时区,以当前会话的时区为准
-- 也就是说
-- - 第一个时间唯一确定
-- - 第二个时间由于不带时区,其表示的时间根据会话的时区不同而不同
select timestamptz '2008-08-08 08:08:08+08', timestamptz '2008-08-08 08:08:08';
--       timestamptz       |      timestamp
-- ------------------------+---------------------
--  2008-08-08 08:08:08+08 | 2008-08-08 08:08:08
-- (1 row)

-- 修改当前会话的时区到火奴鲁鲁的时区 (西十区),写作时未在夏令时
set time zone 'Pacific/Honolulu';
select timestamptz '2008-08-08 08:08:08+08', timestamptz '2008-08-08 08:08:08';
--       timestamptz       |      timestamp
-- ------------------------+---------------------
--  2008-08-07 14:08:08-10 | 2008-08-08 08:08:08
-- (1 row)

如果要将表中数据类型为 timestamp 的列转为 timestamptz 则可以:

-- 假设要被转换的 timestamp 列存了 UTC 时间
alter table <table-name>
    alter <col-name> type timestamptz
        using <col-name> at time zone 'UTC';

2. 日期

如果只关注日期而不关注时间,那么可以使用 date 这个数据类型,它占 4 字节,范围是 4713 BC - 587489 AD,分辨率是 1 天,并且没有时区概念。

date, timestamp, timestamptz 可以互相比较和转换:

set time zone 'Asia/Shanghai';

select timestamp '2008-08-08 00:00:00' = date '2008-08-08'; -- True

select timestamp '2008-08-08 00:00:00.000001' = date '2008-08-08'; -- False

select timestamptz '2008-08-08 00:00:00+08' = date '2008-08-08'; -- True

select timestamptz '2008-08-08 00:00:00+12' = date '2008-08-08'; -- False

set 命令设置了客户端的时区,那么 date 在和 timestamptz 比较时会自动转成 timestamptz 类型,即 2008-08-08 被转为 2008-08-08 00:00:00+08 。而 2008-08-08 00:00:00+12 会先被转成 2008-08-07 18:00:00+08 再和后面的值比较。

3. 时间

如果只关注一天中的某个点,而不关注到底是哪天,可以用 time 类型,它占 8 字节,存储范围是 00:00:00 - 24:00:00,分辨率 1 微秒。timetz 是为了完整实现 SQL 标准而做出来的数据类型,PG 不推荐使用它。

时间戳可以单向转换为时间:

set time zone 'Asia/Shanghai';

select timestamptz '2008-08-08 00:00:59+11'::time with time zone; -- 21:00:59+08

select timestamptz '2008-08-08 00:00:59+11'::time; -- 21:00:59+08

-- 无时区时间不能转带时区时间
select timestamp '2008-08-08 00:00:59'::time with time zone; -- ERROR

select timestamp '2008-08-08 00:00:59'::time; -- 00:00:59

4. 时间长度

interval 这个类型可以用来存储时间长度,它占 16 字节,存储范围是 +-178,000,000 年,分辨率是 1 微秒。

比起存储,它更多地被用于计算时间推移:

-- 北京奥运会开幕到2020元旦有多久了?
select timestamptz '2020-01-01 00:00:00+08' - timestamptz '2008-08-08 08:00:00+08'; -- 4162 days 16:00:00

-- 多少天?
select extract(days from
    timestamptz '2020-01-01 00:00:00+08' - timestamptz '2008-08-08 08:00:00+08'); -- 4162

-- 2020 年元旦的 12 年 12 天 12 小时之前是什么时候? (不关注时区)
select timestamp '2020-01-01 00:00:00' - interval '12 years 12 days 12 hours'; -- 2007-12-19 12:00:00

-- 按会话时区生成 2020 年元旦到 2021 年元旦,每 59 天的序列
select generate_series(timestamptz '2020-01-01 00:00:00+08',
    timestamptz '2021-01-01 00:00:00+08', interval '59 days');
--     generate_series
-- ------------------------
--  2020-01-01 00:00:00+08
--  2020-02-29 00:00:00+08
--  2020-04-28 00:00:00+08
--  2020-06-26 00:00:00+08
--  2020-08-24 00:00:00+08
--  2020-10-22 00:00:00+08
--  2020-12-20 00:00:00+08
-- (7 rows)

5. 时间范围

时间范围常用于预定类业务,可以大幅简化应用层代码。

tsrange, tstzrange 和 daterange 用于存储时间范围,它们采用的底层格式分别是 timestamp, timestamptz 和 date。

-- 可以使用开闭区间来表示是否包含边界
select '[2020-01-01 00:00:00+08, 2020-01-01 12:25:00+08)'::tstzrange;

-- infinity 表示无限
-- infinity 一侧的开闭区间可以随意选择,但在数学上通常写为开区间
select '[2020-01-01 00:00:00+08, infinity)'::tstzrange;

-- 两个区间是否相交
select '[2020-01-01 00:00:00+08, 2020-01-01 12:25:00+08]'::tstzrange
    && '[2020-01-01 00:00:00+08, 2020-01-01 17:15:00+08)'::tstzrange; -- True

-- 求交集
select '[2020-01-01 00:00:00+08, 2020-01-01 12:25:00+08]'::tstzrange
    * '[2020-01-01 08:00:00+08, 2020-01-01 17:15:00+08)'::tstzrange;
--                       ?column?
-- ---------------------------------------------------
--  ["2020-01-01 08:00:00+08","2020-01-01 12:25:00+08"]

-- 求差集
select '[2020-01-01 00:00:00+08, 2020-01-01 12:25:00+08]'::tstzrange
    - '[2020-01-01 08:00:00+08, 2020-01-01 17:15:00+08)'::tstzrange;
--                       ?column?
-- -----------------------------------------------------
--  ["2020-01-01 00:00:00+08","2020-01-01 08:00:00+08")

比如,对于一个会议室预定系统来说,每个会议室在同一时间段只能预约一次,而且时间段不能重叠:

-- PG 自带扩展,为普通类型添加 GIST 索引运算符支持
CREATE EXTENSION btree_gist;

-- 会议室预定表
CREATE TABLE meeting_room
(
    id      SERIAL PRIMARY KEY,
    user_id INTEGER,
    room_id INTEGER,
    range   tsrange,
    EXCLUDE USING GIST(room_id WITH = , range WITH &&)
);

-- 用户 1 预定 101 号会议室,从早上 10 点到下午 6 点
INSERT INTO meeting_room(user_id, room_id, range)
VALUES (1,101, tsrange('2019-01-01 10:00', '2019-01-01 18:00'));

-- 用户 2 预定 101 号会议室,下午 4 点到下午 6 点
INSERT INTO meeting_room(user_id, room_id, range)
VALUES (2,101, tsrange('2019-01-01 16:00', '2019-01-01 18:00'));

-- 用户2的预定报错,违背了排它约束
ERROR:  conflicting key value violates exclusion constraint "meeting_room_room_id_range_excl"
DETAIL:  Key (room_id, range)=(101, ["2019-01-01 16:00:00","2019-01-01 18:00:00")) conflicts with existing key (room_id, range)=(101, ["2019-01-01 10:00:00","2019-01-01 18:00:00")).


Last Update: 2024-03-10 Sun 22:22

Generated by: Emacs 28.2 (Org mode 9.5.5)   Contact: lsz.sino@outlook.com

若正文中无特殊说明,本站内容遵循: 知识共享署名-非商业性使用-相同方式共享 4.0 国际许可协议