Postgres to_timestamp function

Question

I have a python script that insert hourly readings into a postgres db. It is failing in 2010-03-28. How is postgres interpreting both 01:00:00 and 02:00:00 as 02:00:00. what am I doing wrong (ps: works for other dates prior to this)

 select to_timestamp('28/03/2010 01:00:00','DD/MM/YYYY HH24:MI:SS');
      to_timestamp
------------------------
 2010-03-28 02:00:00+01
(1 row)

select to_timestamp('28/03/2010 02:00:00','DD/MM/YYYY HH24:MI:SS');
      to_timestamp
------------------------
 2010-03-28 02:00:00+01
(1 row)

I fail to reproduce this behavior in postgresql 12. I suggest you fold both selects into a single one and check the output ( `select to_timestamp('28/03/2010 01:00:00','DD/MM/YYYY HH24:MI:SS') one, to_timestamp('28/03/2010 02:00:00','DD/MM/YYYY HH24:MI:SS') two, version() v;` ). The query also informs about the postgresql version you are running. — collapsar, Apr 05 '20 at 15:32
In which timezone is your server? It might be related to DST switching (although that happens at 02:00, not at 01:00 — , Apr 05 '20 at 16:02

score 0 · Answer 1 · answered Apr 05 '20 at 16:52

what am I doing wrong?

Nothing wrong.

As commented by a_horse_with_no_name, what you are seeing is the effect of Daylight Saving Time. On 2 AM CET on March 28th, the clock skips one hour and goes directly to 3 AM. I assume that your timezone is UTC+01, so you are seeing this on 1 AM > 2 AM.

That particular day only has 23 hours: both dates do represent the same point in time, which is what you are seeing in the results generated by to_timestamp().

Extracting the corresponding epochs, you can also see that the results are identical:

select 
    to_timestamp('28/03/2010 01:00:00','DD/MM/YYYY HH24:MI:SS') "1 AM",
    to_timestamp('28/03/2010 02:00:00','DD/MM/YYYY HH24:MI:SS') "2 AM",
    extract(epoch from to_timestamp('28/03/2010 01:00:00','DD/MM/YYYY HH24:MI:SS')) "1 AM epoch",
    extract(epoch from to_timestamp('28/03/2010 02:00:00','DD/MM/YYYY HH24:MI:SS')) "2 AM epoch"

1 AM                   | 2 AM                   | 1 AM epoch | 2 AM epoch
:--------------------- | :--------------------- | :--------- | :---------
2010-03-28 02:00:00+01 | 2010-03-28 02:00:00+01 | 1269738000 | 1269738000

This is just one of the many tricks involved when working with dates. There is not much you can do about that from Postgres' perspective: it is on your application to handle that.

score 0 · Answer 2 · answered May 22 '20 at 17:42

I'm afraid that I disagree with the statement that you're doing nothing wrong.

If your database timezone is set to a timezone thats changes to daylight savings at 01:00 on 2010-03-28, e.g., 'Europe/London', then you shouldn't be attempting to create records for both 01:00:00 and 02:00:00 on 2010-03-28, as 01:00:00 does not exist (the time jumps from 00:59:59 to 02:00:00).

If you're not meant to be following daylight savings, then you need to change the DB server timezone to one that doesn't, e.g., 'GMT' or 'UTC'.

Postgres to_timestamp function

2 Answers2