3

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?

Erwin Brandstetter
  • 175,982
  • 27
  • 439
  • 600
Harshal Mahajan
  • 185
  • 1
  • 2
  • 8
  • 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 09: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 09:53
  • 2
    can sou shiow us the Statement you use and the Statement to create tha table? – miracle173 Mar 30 '18 at 09:58
  • UPDATE details SET interval_start_timestamp = '03/11/2018 02:00:00'::TIMESTAMP WHERE id = 2395 – Harshal Mahajan Mar 30 '18 at 10:17
  • Show us the \d details output as well (edit the question and add it).# – ypercubeᵀᴹ Mar 30 '18 at 11:20
  • 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): https://www.timeanddate.com/time/change/usa/denver – ypercubeᵀᴹ Mar 30 '18 at 11:24
  • @ypercubeᵀᴹ I also feel same but do you have any solution for this issue ? 2018-03-11 means 11 march 2018 in this scenario – Harshal Mahajan Mar 30 '18 at 11:32
  • Well, did you read my comment about DST? It is not possible to enter a time between '2018-03-11 02:00:00' and '2018-03-11 02:59:59' in that timezone. Clocks in that timezone never showed any of these times. If you have a '2018-03-11 02:30:00' timestamp then it must have come from a server that was running in a different timezone. – ypercubeᵀᴹ Mar 30 '18 at 11:39
  • Thank you for sharing the link @ypercubeᵀᴹ, At that day 02:00:00 and 03:00:00 time doesn't exist so it is issue due to the server running on different time zone. – Harshal Mahajan Mar 30 '18 at 12:20
  • 02:30:00 should result in the same behavior. I suspect a typo in the question? – Erwin Brandstetter Mar 30 '18 at 13:42

1 Answers1

4

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

Erwin Brandstetter
  • 175,982
  • 27
  • 439
  • 600