Background
In many cases applications need to get current Unix timestamp (seconds since 1970-01-01 00:00:00 UTC, also called Unix epoch or Unix time or POSIX time) as the following Linux command shows.
# date -d '2020-01-01' +%s
1577808000
The Extract Epoch Issue
After upgrading PostgreSQL 9.1 to PostgreSQL 11, the same SQL produces different results.
SELECT extract(epoch from '2020-01-01 00:00:00'::timestamp without time zone);
In PostgreSQL 9.1,
postgres=# SELECT version();
version
------------------------------------------------------------------------------------------------
PostgreSQL 9.1.x on xxx
(1 row)
postgres=# SELECT extract(epoch from '2020-01-01 00:00:00'::timestamp without time zone);
date_part
------------
1577808000
(1 row)
postgres=# SELECT to_timestamp(extract(epoch from '2020-01-01 00:00:00'::timestamp without time zone));
to_timestamp
------------------------
2020-01-01 00:00:00+08
(1 row)
However in PostgreSQL 11, the outcome is NOT same !
postgres=# SELECT version();
version
------------------------------------------------------------------------------------------------
PostgreSQL 11.x on xxx
(1 row)
postgres=# SELECT extract(epoch from '2020-01-01 00:00:00'::timestamp without time zone);
date_part
------------
1577836800
(1 row)
postgres=# SELECT to_timestamp(extract(epoch from '2020-01-01 00:00:00'::timestamp without time zone));
to_timestamp
------------------------
2020-01-01 08:00:00+08
(1 row)
Bug or Feature
After exploring PostgreSQL 9.1 and PostgreSQL 9.2 documentations, we can get some clues.
epoch
For
date
andtimestamp
values, the number of seconds since 1970-01-01 00:00:00 UTC (can be negative); forinterval
values, the total number of seconds in the interval
epoch
For
timestamp with time zone
values, the number of seconds since 1970-01-01 00:00:00 UTC (can be negative); fordate
andtimestamp
values, the number of seconds since 1970-01-01 00:00:00 local time; forinterval
values, the total number of seconds in the interval
As you can see, starting from PostgreSQL 9.2, it distinguishes between timestamp without time zone and timestamp with time zone, while PostgreSQL 9.1 not.
It makes sense as epoch is the seconds since 1970-01-01 00:00:00 UTC which is a timestamp with time zone.
When extracting epoch from a timestamp without time zone, strictly speaking, it should not use the word ‘epoch’ which always relates to UTC time zone.
Say, when extracting seconds from a timestamp without time zone, it will return the number of seconds since 1970-01-01 00:00:00 local time
Let’s try to explain the weird result in PostgreSQL 11 when extracting seconds from a timestamp without time zone based on the documentation.
As shown below, the offset is 28800 seconds, i.e. 8 hours, which is exactly the same utc_offset
of the local time zone PRC.
postgres=# SELECT version();
version
----------------------------------------------------------------------------