Postgresql – Update value in timestamptz column

postgresqlpostgresql-9.5timetimestamptimezone

I have a table column with the data type timestamptz. Time zone of server is America/Denver. Going through some strange issue while inserting a value in that column.

When I update the column to the value '03/11/2018 02:00:00' it results in '03/11/2018 03:00:00' (+ 1 hour!).

UPDATE details 
SET    interval_start_timestamp = '03/11/2018 02:00:00'::TIMESTAMP 
WHERE  id = 2395

The issue is only with the time 02:00:00. 02:30:00 and other values like 01:00:00 or 01:30:00 are inserting properly.

PostgreSQL 9.5.7 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 4.8.4-2ubuntu1~14.04.3) 4.8.4, 64-bit

Any one can help me to understand this behavior?
And how can I insert the proper values?

Best Answer

You fell victim to a cascade of bad ideas.

1. DateStyle

Do not use locale-dependent format in your statements if you can avoid it. If locale settings change (lc_time and DateStyle in particular), your statement suddenly does something different. '03/11/2018' is ambiguous unless coupled with your specific settings. Always use ISO format which is unambiguous and does not depend on additional settings: '2018-03-11'.

2. timezone

Do not assign a timestamp value (or literal) to a timestamptz column. The assignment cast depends on the current timezone setting of your session which introduces another dependency. With a different timezone setting your statement does something different. Use an explicit cast or a timestamp with time zone literal (timestamptz). In your case:

UPDATE details 
SET    interval_start_timestamp = '2018-03-11 02:00:00'::timestamp AT TIME ZONE 'America/Denver'
WHERE  id = 2395

Using an actual time zone name (not an abbreviation) accounts for DST reliably - if your OS is up to date as Postgres works with information provided by the underlying OS.

Related:

3. DST

Which results in '2018-03-11 03:00:00'. As ypercube pointed out, you hit the start of Summer Time a.k.a. Daylight Savings Time. Clocks were turned forward 1 hour at this time. Each time between 02:00:00 and 02:59:59.999999 is interpreted to mean something between 03:00:00 or 03:59:59.999999 respectively, by the silly rules of DST.

The mere existence of "daylight saving time" (DST) is an insult to reason. It should be abolished and never be spoken of again (except for historic time values doomed to mess with this forever).

Test: 

SELECT '2018-03-11 02:34:00'::timestamp AT TIME ZONE 'America/Denver' AT TIME ZONE 'America/Denver' AS t2
     , '2018-03-11 03:34:00'::timestamp AT TIME ZONE 'America/Denver' AT TIME ZONE 'America/Denver' AS t3;

t2                  | t3                 
:------------------ | :------------------
2018-03-11 03:34:00 | 2018-03-11 03:34:00

dbfiddle here

Related Question