PostgreSQL 中的时间

Last Update: 2023-08-20

时间戳

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';

日期

如果只关注日期而不关注时间，那么可以使用 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 再和后面的值比较。

时间

如果只关注一天中的某个点，而不关注到底是哪天，可以用 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

时间长度

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)

时间范围

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

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")).

PostgreSQL 中的时间

目录

时间戳

日期

时间

时间长度

时间范围