We convert time stamps to epoch, do some math on them and then convert them back to time stamps. All times in the database are TIMESTAMP WITHOUT TIME ZONE
.
Since the switch to summer time here in the UK times are off by one hour on one server but not on the other so I did a little test:
SHOW SERVER_VERSION;
SHOW TIMEZONE;
SELECT extract(EPOCH FROM TIMESTAMP '1970-01-01');
On one server I get
server_version
----------------
9.1.15
(1 row)
TimeZone
----------
GB
(1 row)
date_part
-----------
0
(1 row)
But on the other
server_version
----------------
9.3.6
(1 row)
TimeZone
----------
GB
(1 row)
date_part
-----------
-3600
(1 row)
Are there any server settings which could be causing this?
Or did the behaviour of extract
change after Postgres 9.1?
Yes, the behavior of extract
changed in PostgreSQL version 9.2. From the release notes:
Make
EXTRACT(EPOCH FROM timestamp without time zone)
measure the epoch from local midnight, not UTC midnight (Tom Lane)This change reverts an ill-considered change made in release 7.3. Measuring from UTC midnight was inconsistent because it made the result dependent on the
timezone
setting, which computations fortimestamp without time zone
should not be. The previous behavior remains available by casting the input value totimestamp with time zone
.
This might be what is causing the difference, because according to the docs,
The SQL standard requires that writing just
timestamp
be equivalent totimestamp without time zone
, and PostgreSQL honors that behavior.
As @unique_id suggests, using timestamp with time zone
(a.k.a. timestamptz
) should remove the inconsistency.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With