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它会使用更多存储空间。其实，timestamp和timestamptz在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一样没有时区概念。
date、timestamp、timestamptz可以互相比较和转换。
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)
|
时间范围
时间范围常用于预定类业务，可以大幅简化应用层代码。
tsrange、tstzrange和daterange用于表达时间范围，它们分别由timestamp、timestamptz和date构成。
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范例可点击这里获取。
参考文献
