学会和PostgreSQL的时间数据类型愉快玩耍

      ☕ 7 分钟

PostgreSQL(下文简称PG)是一个好数据库,善用它的力量能够事半功倍。

PG提供了多个日期时间数据类型以满足生产需要,有时间戳(timestamp/timestamp with time zone)、日期(date)、一天当中的时间(time)、时间长度(interval)和时间范围(tsrange, tstzrange, daterange)五大类。本文将介绍它们的功能和应用,同时聊一聊推荐的实现方案(例如为什么不应将时间转换为UTC再存储),以及应当避开的误区。

时间戳

我们生活在一个由长、宽、高、时间构成的四维空间中,时间戳记录的是时间轴上面的一点,这一点有确定的时区下的年月日时分秒。

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

  • timestamp,别名timestamp without time zone,时区不敏感;
  • timestamptz,别名timestamp with time zone,时区敏感。

先说结论,在绝大多数情况下,你都应该使用timestamptz(timestamp with time zone),你不该将时间转换为UTC再存储,PG官方也是这么推荐的

一个常见的误解是,因为timestamptz要存储时区,所以比起timestamp它会使用更多存储空间。其实,timestamptimestamptz在PG中都占用相同的存储空间(8字节),有着相同的数据范围(4713 BC~294276 AD),相同的分辨率(1微秒)。PG在存储timestamptz时,会自动将输入的时间转换为UTC时区来存储,查询时会自动转为客户端时区来展示;而timestamp的存储和展现完全无视时区,需要客户端自己来造轮子处理。

那为什么不选用timestamp,让程序先自己转UTC再存储,读取时读UTC再自己转换为需要的时区呢?

原因有三:

  • 更多的代码,更多的负担,换来一个数据库已经提供的功能;
  • PG非常擅长记住时区信息和夏令时信息(试试select * from pg_timezone_names;),你自己进行转换可能没它做得好;
  • timestamptz能够让date_trunc()generate_series()等函数在你的时区下工作,你可以取到“正确的某一天的开头”,譬如,按你的时区(而不是UTC)来按天聚合账目数据。

但是这世上没有万金油,一些情况下,timestamptz也没有那么讨喜:

  • 如果现存的业务和代码重度依赖timestamp,引入新的类型timestamptz可能会带来混乱,增加开发时的思维负担;
  • 非常关注未来的本地时间(例如日历),而本地时区在以后可能会变化,这个情况下PG的轮子也帮不上忙。
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
-- 设置当前客户端会话的时区
-- 会话时区也可以在数据库连接字符串(DSN)中指定,
-- 例如:timezone=Asia/Chongqing
set time zone 'Asia/Chongqing';

-- 显示当前会话的时区
show timezone;
-- 结果:Asia/Chongqing

-- 将字符串解析为带时区的时间,如果没有提供时区,会以当前会话时区为准。
-- 也就是说,第一个时间是唯一确定的(年月日时分秒+时区),第二个不是。
select timestamptz '2008-08-08 08:08:08+08', timestamptz '2008-08-08 08:08:08';
-- 结果:
--      timestamptz       |      timestamptz       
-- ------------------------+------------------------
-- 2008-08-08 08:08:08+08 | 2008-08-08 08:08:08+08
-- (1 row)

-- 修改当前会话的时区到火奴鲁鲁的时区(西十区,写作时未在夏令时)
set time zone 'Pacific/Honolulu';
-- 重复一遍刚刚的查询,我们可以看到同一个时间在不同客户端时区下的表示:
-- 2008-08-08 08:08:08+08 和 2008-08-07 14:08:08-10
select timestamptz '2008-08-08 08:08:08+08', timestamptz '2008-08-08 08:08:08';
-- 结果:
--       timestamptz       |      timestamptz       
-- ------------------------+------------------------
--  2008-08-07 14:08:08-10 | 2008-08-08 08:08:08-10
-- (1 row)

timestamp就像一张内容有日历和时钟的照片,你需要提供时区信息才能让它有意义。相反,timestamptz能在时间轴上唯一地确定一个点。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
set time zone 'Asia/Chongqing';

-- 强制将timestamptz转换为timestamp,
-- 时区会被丢掉,日期和时间在文本上不变。
select timestamptz '2008-08-08 08:08:08+08' :: timestamp;
-- 结果:
--       timestamp      
-- ---------------------
--  2008-08-08 08:08:08
-- (1 row)

-- 强制将timestamp转换为timestamptz,
-- 时间以当前会话的时区进行解析。
select timestamp '2008-08-08 08:08:08' :: timestamptz;
-- 结果:
--       timestamptz       
-- ------------------------
--  2008-08-08 08:08:08+08
-- (1 row)

-- 所以这些比较结果就在意料之中了
select timestamp '2008-08-08 08:08:08' = timestamptz '2008-08-08 08:08:08+08';
-- 结果:真

select timestamp '2008-08-08 08:08:08' = timestamptz '2008-08-08 08:08:08+06';
-- 结果:假

-- 2008-08-08 08:08:08+08 是罗马的几点钟?
-- 使用 at time zone 能够进行转换,注意此时输出的类型是 timestamp
select timestamptz '2008-08-08 08:08:08+08' at time zone 'Europe/Rome',
       pg_typeof(now() at time zone 'Europe/Rome');
-- 结果:
--       timezone       |          pg_typeof          
-- ---------------------+-----------------------------
--  2008-08-08 02:08:08 | timestamp without time zone
-- (1 row)

-- 将字面时间加上时区
select timestamp '2008-08-08 08:08:08' at time zone 'Asia/Chongqing',
       pg_typeof(timestamp '2008-08-08 08:08:08' at time zone 'Asia/Chongqing');
-- 结果:
--         timezone        |        pg_typeof         
-- ------------------------+--------------------------
--  2008-08-08 08:08:08+08 | timestamp with time zone
-- (1 row)

select timestamp '2008-08-08 08:08:08' at time zone 'Europe/Vienna',
       pg_typeof(timestamp '2008-08-08 08:08:08' at time zone 'Europe/Vienna');
-- 结果:
--         timezone        |        pg_typeof         
-- ------------------------+--------------------------
--  2008-08-08 14:08:08+08 | timestamp with time zone
-- (1 row)

如果你正想将表tbl中的ts_column的数据类型从timestamp转换为timestamptz,以享受timestamptz带来的各种便利:

1
2
3
4
5
-- 这里假设timestamp是按UTC时区来记录的,
-- 根据你的情况具体调整。
alter table tbl
    alter ts_column type timestamptz
        using ts_column at time zone 'UTC';

日期

如果你只关注日期而不关注时分秒,可以使用date数据类型,它占4个字节,范围从4713 BC到5874897 AD,分辨率为1天,和timestamp一样没有时区概念。

datetimestamptimestamptz可以互相比较和转换。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
set time zone 'Asia/Chongqing';

-- date类型在和时间戳比较和转换时,时分秒视为0
select timestamp '2008-08-08 00:00:00' = date '2008-08-08';
-- 结果:真

select timestamp '2008-08-08 00:00:00.000001' = date '2008-08-08';
-- 结果:假

-- date类型不带时区,时区按当前会话的时区进行解析
select timestamptz '2008-08-08 00:00:00+08' = date '2008-08-08';
-- 结果:真

select timestamptz '2008-08-08 00:00:00+12' = date '2008-08-08';
-- 结果:假

一天当中的时间

如果你只关注时分秒,而不关注年月日,可以考虑使用time(8字节)和time with time zone(12字节),它们存储00:00:00到24:00:00,分辨率为1微秒。

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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
set time zone 'Asia/Chongqing';

-- time with time zone 时区敏感
select timestamptz '2008-08-08 00:00:59+11'::time with time zone;
-- 结果:21:00:59+08

-- timestamp 无法转换为 time with time zone,
-- 这个语句会报错。
select timestamp '2008-08-08 00:00:59'::time with time zone;

-- time 时区不敏感,转换时按会话时区解析时间后丢掉时区
select timestamptz '2008-08-08 00:00:59+11'::time;
-- 结果:21:00:59

-- 直接提取timestamp的时间部分
select timestamp '2008-08-08 00:00:59'::time;
-- 结果:00:00:59

时间长度

interval用于在PG中表示时间长度,占16字节,范围正负178000000年,分辨率1微秒。

比起存储,它更经常用于时间推移的计算。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
-- 北京奥运会开幕到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)

时间范围

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

tsrangetstzrangedaterange用于表达时间范围,它们分别由timestamptimestamptzdate构成。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
-- 左闭右开区间
select '[2020-01-01 00:00:00+08, 2020-01-01 12:25:00+08)' :: tstzrange;

-- 全闭区间
select '[2020-01-01 00:00:00+08, 2020-01-01 12:25:00+08]' :: tstzrange;

-- 只限制一侧,另一侧为无限
select '[2020-01-01 00:00:00+08, infinity)' :: tstzrange;

-- 是否包含
select '[2020-01-01 00:00:00+08, infinity)' :: tstzrange @> '[2020-01-05 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 08:00:00+08, 2020-01-01 17:15:00+08]' :: tstzrange;
-- 结果:真

-- 求交集
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;
-- 结果:["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;
-- 结果:["2020-01-01 00:00:00+08","2020-01-01 08:00:00+08"]

设想你在实现一个会议室预约系统,每个会议室在同一个时段只能预约一次,而且时段不能重叠。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- 启用btree_gist扩展
create extension btree_gist;

-- 创建预约表
create table room_reservation
(
    room   text,
    during tsrange,
    exclude using GIST (room with =, during with &&)
);

-- 预约101A的下午时段,OK
insert into room_reservation
values ('101A', '[2020-01-01 14:00, 2020-01-01 15:35)');

-- 预约203B的下午同一时段,OK
insert into room_reservation
values ('203B', '[2020-01-01 14:00, 2020-01-01 15:35)');

-- 尝试预约101A的下午时段,和之前的预约时间不完全重叠
insert into room_reservation
values ('101A', '[2020-01-01 11:00, 2020-01-01 15:00)');
-- 错误:  互相冲突的键值违反排他约束"room_reservation_room_during_excl"
-- 描述:  键(room, during)=(101A, ["2020-01-01 11:00:00","2020-01-01 15:00:00"))与已存在的键(room, during)=(101A, ["2020-01-01 14:00:00","2020-01-01 15:35:00"))冲突

本文中涉及的SQL范例可点击这里获取。

参考文献

分享

nanmu42
作者
nanmu42
用心构建美好事物。

目录