2

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?

  • I don't understand your problem description. Are you updating or inserting? Does it mean you insert a row where this column has the value '03/11/2018 02:00:00' and when you then retrieve this row the columns shows '03/11/2018 03:00:00' ? – miracle173 Mar 30 '18 at 9:48
  • I am updating the value with '03/11/2018 02:00:00' but it is inserting as '03/11/2018 03:00:00' while I want to insert the value for what i have given and that should not convert it into timestamp. – Harshal Mahajan Mar 30 '18 at 9:53
  • 2
    can sou shiow us the Statement you use and the Statement to create tha table? – miracle173 Mar 30 '18 at 9:58
  • 1
    Also: is that ambiguous '03/11/2018 ...' meant to be 3rd of November or 11th of March? The value entered into the table will depend on various settings when you use ambiguous formats. – ypercubeᵀᴹ Mar 30 '18 at 11:22
  • 2
    The issue is probably caused by the DST change that happened on '2018-03-11 02:00:00' (clocks moved 1 hour forward): timeanddate.com/time/change/usa/denver – ypercubeᵀᴹ Mar 30 '18 at 11:24
3

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.