In this post I’m reading official PostgreSQL documentation related to date and time types, and taking notes. In case you know this stuff: nothing fancy here. However, if you need a refresher - then: go ahead and read, you’re welcome!

PostgreSQL has several types which you can use to work with dates/times:

• timestamp without time zone (same as timestamp) and timestamp with time zone
• date
• time without time zone (same as time) and time with timezone
• interval

Types

Values of the above types use up different amount od space on the storage, have different ranges and resolutions (date has resolution of one day, other types have a resolution of one microsecond) and, of course, different semanics.

Heres’s the list of date/time types (copied from docs):

Optional (p) parameter (of possible values 0 to 6 incl.) denotes the number of decimal places in seconds field (by default no restriction is imposed).

 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

postgres=# select time  '11:12:23.1234567';
      time       
-----------------
 11:12:23.123457
(1 row)

postgres=# select time (6) '11:12:23.1234567';
      time       
-----------------
 11:12:23.123457
(1 row)

postgres=# select time (2) '11:12:23.1234567';
    time     
-------------
 11:12:23.12
(1 row)

postgres=# select time (7) '11:12:23.1234567';
WARNING:  TIME(7) precision reduced to maximum allowed, 6
LINE 1: select time (7) '11:12:23.1234567';
               ^
      time       
-----------------
 11:12:23.123457
(1 row)

Type interval may also use fields option which is one of the following (and denotes precision of the interval):

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

YEAR
MONTH
DAY
HOUR
MINUTE
SECOND
YEAR TO MONTH
DAY TO HOUR
DAY TO MINUTE
DAY TO SECOND
HOUR TO MINUTE
HOUR TO SECOND
MINUTE TO SECOND

For example, difference between two dates is expressed as a value of type iterval:

1
2
3
4
5

postgres=# select timestamp '2021-12-24 19:00:00' - now() as "time to Cristmas Eve";
  time to Cristmas Eve   
-------------------------
 49 days 00:38:57.875692
(1 row)

Usages

• For representing dates you should use date type.
• For precise moment in time - one of time variants (unless you need tz info, in which case see above).
• For expressing the moment in time when an event took place - use variants of timestamp.
• For saving durations (without telling the begining or ending time) - use interval.

Input

Date input

PostgreSQL is very liberal and accepting when it comes to parsing incoming literals for dates/times.

It accepts dates in ISO 8601, dates in both YMD (year, month, day) and MDY (month, day, year) ordering and also julian calendar dates.

Generally speaking, time-related literals all have this common form:

type [(p)] ‘value’

Note that literal value is enclosed in single quotes. For example:

1
2
3
4

date '2021-11-04'
date 'October 4, 2021'
date '20211104'
date '11/04/2021'

Time input

Time types have an optional timezone, which can be expressed as an offset from UTC (prefixed with either + or -), as an abbreviation and also as a full timezone name:

1
2
3

time with time zone '04:05:06-08:00'
time with time zone '04:05:06 PST'
time with time zone '2003-04-12 04:05:06 America/New_York'

Timestamp input

Timestamp literal consists of date, time, optional offset and optional AD/BC sufffix. Timestamp and timezone should be separated with + or -, e.g.

1
2

timestamp with time zone '2004-10-19 10:23:54+02'
timestamp with time zone '2004-10-19 10:23:54-02'

PostgreSQL recognizes the type based on the declaration and does not parse the literal. Therefore this delcaration:

1

timestamp '2004-10-19 10:23:54-02'

will be of type timestamp without time zone (and timezone info will be lost).

Special literals

As a time literal you can also use following values (not forgetting about single quotes):

Interval input

Intervals can be input in two ways:

1. In non-standard way as:

[@] quantity unit [quantity unit…] [direction] where

• symbol @ is an “optional noise”
• quantity is a signed numeric value
• unit is one of following values (which may be either singular or plural): microsecond, millisecond, second, minute, hour, day, week, month, year, decade, century, millennium

where days, hours, minutes and seconds can be expressed without units, separated accordingly, e.g.:

• ‘200-10’ means ‘200 years 10 months’
• ‘1 12:59:10’ means ‘1 day 12 hours 59 min 10 sec’

2. In standard way (ISO 8601):

P quantity unit [ quantity unit …] [ T [ quantity unit …]]

or alternatively:

P [ years-months-days ] [ T hours:minutes:seconds ]

where P is mandatory and parts less than one day needs to appear after (in this case mandatory, too) T.

For example:

• P1Y2M3DT4H5M6S
• P0001-02-03T04:05:06

Here are the unit shorcuts for interval definition (ISO 8601):
Y 	Years
M 	Months (in the date part)
W 	Weeks
D 	Days
H 	Hours
M 	Minutes (in the time part)
S 	Seconds

Output - writing out dates/times

According to SQL standard, dates should be printed in ISO 8601 standard. PostgreSQL makes one small nonstandard change: instead of letter T separatinf date from time it uses (according to RFC3339) a space. Beside that, it also allows to write out dates in on eof following formats:

• ISO (ISO 8601) SQL standard, e.g. 1997-12-17 07:37:16-08
• SQL (traditional), e.g. 12/17/1997 07:37:16.00 PST
• Postgres (original), e.g. Wed Dec 17 07:37:16 1997 PST
• German (regional), e.g. 17.12.1997 07:37:16.00 PST

Even more sophisticated ways of formatting date/time output are possible. You can use formatting functions, for example:

• to_char ( timestamp, text ) → text - to convert a timestamp to string with given format, e.g.:
  • to_char(timestamp ‘2002-04-20 17:31:12.66’, ‘HH12:MI:SS’) → 05:31:12
• to_date ( text, text ) → date - to convert string to date/time with given format, e.g.:
  • to_date(‘05 Dec 2000’, ‘DD Mon YYYY’) → 2000-12-05

All formats you can use to build such formats for parsing/printing can be found in the docs.

Time zones.

Living on an almost round Earth which axis rotation takes around 24 hours, split to countries which sometimes manage to come to a common conclusion regarding, for example, time standars, is… well, is complicated. Let’s look at some of those complexities.

Time zones depend not only on the shape of the Earth but also on political decisions. At the early 1900 most aspects of time zones were more or less standardixed, but introduction of DST (daylight saving time)complicated time usage and calculations even further.

Time zone rules

• For dates in the past PostgreSQL uses zone rules from IANA (Olson) database
• For dates in the future it extrapolates existing rules (as if it assumed they will not change)

Ways of specifying timezone

There are three ways of specifying a timezone:

• full name of the time zone; the names are stored in system table pg_timezone_names, e.g. Europe/Warsaw - this name comes from IANA database and is widely used and recognized in different context (systems, libraries etc)
• abbreviation of timezone, e.g. CET; this specifies absolute offset in relation to UTC timezone (while full zone name usually considers also DST changes (see pg_timezone_abbrevs)
• time zone names according to POSIX standard, see POSIX

See timezone names tables

Full name accounts for DST

The difference between abbreviated and full name might have a meaning. Using an abbreviation is eqiuvalent to using numeric offset. Using full name of a timezone means that DST changes will be applied. Then, for example, the timestamp with timezone

1

timestamptz '2021-10-31 02:00 Europe/Warsaw' 

means 2 a.m. in CET i and this is the same moment in time as

1

timestamptz '2021-10-31 02:00 CET'

Here’s the proof:

1
2
3
4
5

postgres=# select timestamptz '2021-10-31 02:00 CET' - timestamptz '2021-10-31 02:00 Europe/Warsaw';
 ?column? 
----------
 00:00:00
(1 row)

However, as you might know, at this moment (‘2021-10-31 02:00 CET’ is ‘2021-10-31 01:00 UTC’) Poland “moved” from summer time (UTC+2) to winter time (UTC+1), so we “moved” our clocks back from 3:00 a.m. (when we’ve been in DST - summer time) to 2:00 a.m. which means we lived 2:00-3:00 hour “twice” hence could sleep one hour longer.

So one hour and one minute earlier we had almost the same time (according to local clock: timestamptz ‘2021-10-31 01:59 Europe/Warsaw’, but it was in fact one hour and one minute earlier:

1
2
3
4
5

postgres=# select timestamptz '2021-10-31 01:59 CET' - timestamptz '2021-10-31 01:59 Europe/Warsaw';
 ?column? 
----------
 01:00:00
(1 row)

At 02:59 CEST Europe/Warsaw geographical zone was CEST (CEST = UTC+2) and it was DST. Then, at 03:00 CEST we “switched zones” to CET (CET = UTC+1) and our clocks started to show 2:00.

So, how should we specify the time of meeting? If someone from London says: “Let’s talk on 02:30 Warsaw time” then I’m not sure what they mean because that day I will encounter 2:30 twice on my wall clock. So the person should better say: “let’s meet at 02:30 CEST” (or: at “00:30 UTC”),

See:

• european time zones
• Rozporządzenie Prezesa Rady Ministrów z dnia 3 listopada 2016 r. w sprawie wprowadzenia i odwołania czasu letniego środkowoeuropejskiego w latach 2017–2021
• this discussion and also this one

Timezone names tables

Let’s have a look at following tables:

• pg_timezone_names
• pg_timezone_abbrevs

 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

postgres=# select * from pg_timezone_names limit 30;
           name            | abbrev | utc_offset | is_dst 
---------------------------+--------+------------+--------
 Greenwich                 | GMT    | 00:00:00   | f
 Turkey                    | +03    | 03:00:00   | f
 Kwajalein                 | +12    | 12:00:00   | f
 Antarctica/Syowa          | +03    | 03:00:00   | f
 Antarctica/Macquarie      | AEDT   | 11:00:00   | t
 Antarctica/McMurdo        | NZDT   | 13:00:00   | t
 Antarctica/South_Pole     | NZDT   | 13:00:00   | t
 Antarctica/Casey          | +11    | 11:00:00   | f
 Antarctica/DumontDUrville | +10    | 10:00:00   | f
 Antarctica/Troll          | +00    | 00:00:00   | f
 Antarctica/Palmer         | -03    | -03:00:00  | f
 Antarctica/Rothera        | -03    | -03:00:00  | f
 Antarctica/Vostok         | +06    | 06:00:00   | f
 Antarctica/Mawson         | +05    | 05:00:00   | f
 Antarctica/Davis          | +07    | 07:00:00   | f
 HST                       | HST    | -10:00:00  | f
 GB-Eire                   | GMT    | 00:00:00   | f
 MST7MDT                   | MDT    | -06:00:00  | t
 CET                       | CET    | 01:00:00   | f
 EST                       | EST    | -05:00:00  | f
 Canada/Eastern            | EDT    | -04:00:00  | t
 Canada/Saskatchewan       | CST    | -06:00:00  | f
 Canada/Mountain           | MDT    | -06:00:00  | t
 Canada/Central            | CDT    | -05:00:00  | t
 Canada/Yukon              | MST    | -07:00:00  | f
 Canada/Pacific            | PDT    | -07:00:00  | t
 Canada/Newfoundland       | NDT    | -02:30:00  | t
 Canada/Atlantic           | ADT    | -03:00:00  | t
 Egypt                     | EET    | 02:00:00   | f
 ROC                       | CST    | 08:00:00   | f

 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

postgres=# select * from pg_timezone_abbrevs limit 30;
 abbrev | utc_offset | is_dst 
--------+------------+--------
 ACDT   | 10:30:00   | t
 ACSST  | 10:30:00   | t
 ACST   | 09:30:00   | f
 ACT    | -05:00:00  | f
 ACWST  | 08:45:00   | f
 ADT    | -03:00:00  | t
 AEDT   | 11:00:00   | t
 AESST  | 11:00:00   | t
 AEST   | 10:00:00   | f
 AFT    | 04:30:00   | f
 AKDT   | -08:00:00  | t
 AKST   | -09:00:00  | f
 ALMST  | 07:00:00   | t
 ALMT   | 06:00:00   | f
 AMST   | 04:00:00   | f
 AMT    | -04:00:00  | f
 ANAST  | 12:00:00   | f
 ANAT   | 12:00:00   | f
 ARST   | -03:00:00  | f
 ART    | -03:00:00  | f
 AST    | -04:00:00  | f
 AWSST  | 09:00:00   | t
 AWST   | 08:00:00   | f
 AZOST  | 00:00:00   | t
 AZOT   | -01:00:00  | f
 AZST   | 04:00:00   | f
 AZT    | 04:00:00   | f
 BDST   | 02:00:00   | t
 BDT    | 06:00:00   | f
 BNT    | 08:00:00   | f
(30 rows)

Poland - timezone

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

postgres=# select * from pg_timezone_names where name ilike '%poland%'
postgres-# ;
     name     | abbrev | utc_offset | is_dst 
--------------+--------+------------+--------
 posix/Poland | CET    | 01:00:00   | f
 Poland       | CET    | 01:00:00   | f
(2 rows)

postgres=# select * from pg_timezone_names where name ilike '%warsaw%'
;
        name         | abbrev | utc_offset | is_dst 
---------------------+--------+------------+--------
 Europe/Warsaw       | CET    | 01:00:00   | f
 posix/Europe/Warsaw | CET    | 01:00:00   | f
(2 rows)

I can learn from the above data that:

• shortcut for my timezone is (at this moment) CET (Central Europe Time)
• full name is posix/Poland,Poland, Europe/Warsaw or posix/Europe/Warsaw
• Poland is not in “daylight saving time” period (value f in is_dst column)

Now I wonder: if I had checked the values in those tables two weeks ago, would I have seen the following? Or perhaps the table always show the default abbreviation (CET=UTC+1) for a location and only changes is_dst?

1

 Europe/Warsaw       | CEST    | 02:00:00   | t

Functions

The list of date/time manipulation/creation functions provided by PostgreSQL system is impressive. I think it’s really worth to just look at them and read the docs so that in case you need some of them - you know where to check next time.

For date and time creation

• age ( timestamp, timestamp ) → interval - time difference
• current_date → date - current date
• current_timestamp → timestamp with time zone - current timestamptz (variable)
• now ( ) → timestamp with time zone - current timestamptz (function)
• make_date ( year int, month int, day int ) → date - create date from numeric values
• to_timestamp ( double precision ) → timestamp with time zone - Unix timestamp convertion to timestamptx

For checking interval overlap

There is also a logical operator OVERLAPS (returning true or false) which acts on pairs: (start1, end1) OVERLAPS (start2, end2), e.g.:

• SELECT (DATE ‘2001-02-16’, INTERVAL ‘100 days’) OVERLAPS (DATE ‘2001-10-30’, DATE ‘2002-10-30’);

For details extraction

You can extract details from a timestamp using EXTRACT (something FROM something), e.g.:

• SELECT EXTRACT(QUARTER FROM TIMESTAMP ‘2001-02-16 20:38:40’); → 1
• SELECT EXTRACT(DAY FROM TIMESTAMP ‘2001-02-16 20:38:40’); → 16

For time zone conversions:

Operator AT TIME ZONE converts

• timestamp without time zone to a specific time zone: timestamp ‘2001-02-16 20:38:40’ at time zone ‘America/Denver’ → 2001-02-17 03:38:40+00
• timestamp with time zone to another time zone: time with time zone ‘05:34:17-05’ at time zone ‘UTC’ → 10:34:17+00

Summary

After writing this blog post I:

• know about date and time types available in PostgreSQL system
• know how to input values of those types and how to write them out
• checked in what system tables there are abbreviaitons and full names of time zones
• learned that when using timestamp with timezone it is important to also understand DST and to know that full time zone names “follow” DST changes
• had a look at the list of time-related funcitons

I’ve learned a lot. I hope that you have also learned something new.

Update:

A good resource really worth your time: tapoueh.org: data types

Ten wpis jest częścią serii sql.

• 2021-04-11 - Postgres - dates
• 2021-04-10 - Postgres - JSON
• 2021-04-10 - Postgres - exercises
• 2021-30-09 - Postgres - installation