PostgreSQL(or Postgres) is a great database. Life would be much easier if its strength is well utilized.
Postgres provides five datetime data types, including timestamp/timestamp with time zone, date, time, interval and time range(tsrange, tstzrange, daterange). This article briefs them, talks about gotchas and recommendations like why not store every datetime in UTC.
Timestamps
We are living in a four-dimension space of length, width, height and time. The time is uniquely determined by year, month, day, hour, minute and second, given timezone is specified.
Per SQL standard, Postgres implements and provides two timestamp types:
timestamp with time zone, aka timestamptz, timezone-aware;timestamp without time zone, aka timestamp, not timezone-aware.
Conclusion first, Postgres recommends that timestamptz(timestamp with time zone) should be used under most circumstances and you should not store time as UTC in timestamp.
It’s a common misunderstanding that timestamptz takes more disk spaces than timestamp because of the need to store timezone info. Actually, timestamptz and timestamp take the same amount of disk space(8 bytes), has the same range(4713 BC~294276 AD), and has the same resolution of 1 microsecond. When handling timestamptz, Postgres converts the datetime into UTC automatically before storing and converts back into session timezone during querying. On the contrary, timestamp has no sense of timezone and applications must taken care of this process(build the wheel) by themselves.
Why not store everything in UTC and convert them back when needed by yourself?
There are 3 good reasons:
- More codes, more barriers, in exchange of a feature that database has already provides;
- Postgres is really good at remembering timezone and daylight saving time(try
select * from pg_timezone_names;), and you may fail to do a better job than it; - Functions like
date_trunc() and generate_series() work properly in the context of timestamptz, which enables you to get “correct start time of a day”, e.g, aggregating your accounting data by day in your timezone other than UTC.
However, one cure can not be good for all. There are some circumstances where timestamptz is not preferred:
- The existing businesses and codes have already relied heavily on
timestamp, introducing timestamptz may mix things up and bring mental overhead; - when dealing with localtime in the future(like calendar), where timezone may subject to change, you may want to build the wheel by yourself to be certain.
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
|
-- Set timezone of current session.
-- Session timezone can be determined by DSN, too.
-- Like timezone=Asia/Chongqing
-- Here we use Asia/Chongqing(GMT+8) as example
set time zone 'Asia/Chongqing';
-- Show timezone of current session
show timezone;
-- Result: Asia/Chongqing
-- Parse date and time string to timestamptz,
-- session timezone is used if no timezone provided.
-- That's to say, the first datetime is unique in meaning(timezone included),
-- but the second is not.
select timestamptz '2008-08-08 08:08:08+08', timestamptz '2008-08-08 08:08:08';
-- Result:
-- timestamptz | timestamptz
-- ------------------------+------------------------
-- 2008-08-08 08:08:08+08 | 2008-08-08 08:08:08+08
-- (1 row)
-- Change current session timezone to Honolulu
-- (GMT-10, not in daylight saving as of writing)
set time zone 'Pacific/Honolulu';
-- Repeat the query above, see?
select timestamptz '2008-08-08 08:08:08+08', timestamptz '2008-08-08 08:08:08';
-- Result:
-- timestamptz | timestamptz
-- ------------------------+------------------------
-- 2008-08-07 14:08:08-10 | 2008-08-08 08:08:08-10
-- (1 row)
|
timestamp is like a photo of the calendar and clock, and you must provide a timezone to make it useful. While timestamptz uniquely determines a datetime.
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';
-- Convert timestamptz to timestamp:
-- the timezone is gone, the date and time keeps unchanged literally.
select timestamptz '2008-08-08 08:08:08+08' :: timestamp;
-- Result:
-- timestamp
-- ---------------------
-- 2008-08-08 08:08:08
-- (1 row)
-- Covert timestamp to timestamptz:
-- session timezone is used.
select timestamp '2008-08-08 08:08:08' :: timestamptz;
-- Result:
-- timestamptz
-- ------------------------
-- 2008-08-08 08:08:08+08
-- (1 row)
-- The same rules apply to comparision
select timestamp '2008-08-08 08:08:08' = timestamptz '2008-08-08 08:08:08+08';
-- Result: True
select timestamp '2008-08-08 08:08:08' = timestamptz '2008-08-08 08:08:08+06';
-- Result: False
-- What the time is in Rome at 2008-08-08 08:08:08+08?
-- Use "at time zone" to do the conversion, mind the output type is timestamp.
select timestamptz '2008-08-08 08:08:08+08' at time zone 'Europe/Rome',
pg_typeof(now() at time zone 'Europe/Rome');
-- Result:
-- timezone | pg_typeof
-- ---------------------+-----------------------------
-- 2008-08-08 02:08:08 | timestamp without time zone
-- (1 row)
-- Add timezone to timestamp to get timestamptz
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');
-- Result:
-- 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');
-- Result:
-- timezone | pg_typeof
-- ------------------------+--------------------------
-- 2008-08-08 14:08:08+08 | timestamp with time zone
-- (1 row)
|
If you are convinced and try to alter your table column data type from timestamp to timestamptz to gain benefits we’ve mentioned above:
1
2
3
4
5
|
-- Alter column data type from timestamp to timestamptz,
-- assuming timestamp is recorded in UTC.
alter table tbl
alter ts_column type timestamptz
using ts_column at time zone 'UTC';
|
Dates
date type can be used when the time part is not concerned. date takes 4 bytes, ranges from 4713BC to 5874897 AD, has a resolution of 1 day. It gets no sense of timezone like timestamp.
Comparision and conversion can be made among date, timestamp and timestamptz.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
set time zone 'Asia/Chongqing';
-- date has time of 00:00:00 during comparision
-- between timestamp/timestamptz
select timestamp '2008-08-08 00:00:00' = date '2008-08-08';
-- Result: True
select timestamp '2008-08-08 00:00:00.000001' = date '2008-08-08';
-- Result: False
-- current session timezone is used
-- during comparision between timestamptz
select timestamptz '2008-08-08 00:00:00+08' = date '2008-08-08';
-- Result: True
select timestamptz '2008-08-08 00:00:00+12' = date '2008-08-08';
-- Result: False
|
Times of a day
When only time part is focused, time and time with time zone types may be handy. time takes 8 bytes while time with time zone takes 12 bytes. They range from 00:00:00 to 24:00:00 with a resolution of 1 microsecond.
You can do one-way conversion, turning timestamps into times.
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;
-- Result: 21:00:59+08
-- timestamp can not be converted into time with time zone
select timestamp '2008-08-08 00:00:59'::time with time zone;
-- time is not timezone-aware,
-- so it takes the time part of timestamptz at current session timezone.
select timestamptz '2008-08-08 00:00:59+11'::time;
-- Result: 21:00:59
-- time takes time part of timestamp, directly.
select timestamp '2008-08-08 00:00:59'::time;
-- Result: 00:00:59
|
Intervals
interval stands for, well, intervals, it takes 16 bytes, ranges ±178000000 years with a resolution of 1 microseconds.
interval is also used in time diff computations.
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
|
-- How long does it take from Beijing Olympics to 2020 new year's day?
select timestamptz '2020-01-01 00:00:00+08' - timestamptz '2008-08-08 08:00:00+08';
-- Result: 4162 days 16:00:00
-- How many days then?
select extract(days from
timestamptz '2020-01-01 00:00:00+08' - timestamptz '2008-08-08 08:00:00+08');
-- Result: 4162
-- What the time is, 12 years 12 day and 12 hours
-- before 2020 new year's day?
-- Note timezone is not considered here.
select timestamp '2020-01-01 00:00:00' - interval '12 years 12 days 12 hours';
-- Result: 2007-12-19 12:00:00
-- Generate series between
-- 2020 new year's day and 2021 every 59 days,
-- at current session timezone.
select generate_series(timestamptz '2020-01-01 00:00:00+08',
timestamptz '2021-01-01 00:00:00+08', interval '59 days');
-- Result:
-- 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)
|
Time Ranges
Time ranges can simplify application code by a lot in reservation businesses.
tsrange, tstzrange and daterange are Postgres built-in time ranges, consist of timestamp, timestamptz and 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
|
-- Left closed right open range
select '[2020-01-01 00:00:00+08, 2020-01-01 12:25:00+08)' :: tstzrange;
-- Closed range
select '[2020-01-01 00:00:00+08, 2020-01-01 12:25:00+08]' :: tstzrange;
-- Range with one side unbound
select '[2020-01-01 00:00:00+08, infinity)' :: tstzrange;
-- Included?
select '[2020-01-01 00:00:00+08, infinity)' :: tstzrange @> '[2020-01-05 00:00:00+08, infinity)' :: tstzrange;
-- Result: True
-- Intersected?
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;
-- Result: True
-- Intersection
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;
-- Result: ["2020-01-01 08:00:00+08","2020-01-01 12:25:00+08")
-- Difference
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;
-- Result: ["2020-01-01 00:00:00+08","2020-01-01 08:00:00+08"]
|
Assuming you are implementing a meeting room reservation system. A meeting room can be reserved only one time during one period, and overlaping periods for a meeting room is prohibited.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
-- Following example needs btree_gist extension
create extension btree_gist;
-- A table for room reservation
create table room_reservation
(
room text,
during tsrange,
exclude using GIST (room with =, during with &&)
);
-- Reserve 101A for afternoon, OK
insert into room_reservation
values ('101A', '[2020-01-01 14:00, 2020-01-01 15:35)');
-- Reserve 203B for the same period, OK
insert into room_reservation
values ('203B', '[2020-01-01 14:00, 2020-01-01 15:35)');
-- Try to reserve 101A, with a period not entirely overlapped
insert into room_reservation
values ('101A', '[2020-01-01 11:00, 2020-01-01 15:00)');
-- Postgres is going to complain about constraint violation.
|
The SQL examples in this article can be found here.
References
