2

Why does to_timestamp use different timezones to display epochs, in the same query? Note +02 vs +01.

=> select to_timestamp(1651157248),to_timestamp(1647224832);
      to_timestamp      |      to_timestamp      
------------------------+------------------------
 2022-04-28 16:47:28+02 | 2022-03-14 03:27:12+01
(1 row)

=> select version();
                                                                version                                                                 
----------------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 12.12 (Ubuntu 12.12-0ubuntu0.20.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.4.0-1ubuntu1~20.04.1) 9.4.0, 64-bit
(1 row)

=> show timezone;
   TimeZone    
---------------
 Europe/Vienna
(1 row)

It may be related to the switch from winter to summer time at the end of March, with +01 corresponding to CET and +02 to CEST.

How do I use to_timestamp such that it uses consistently the same time offset (no matter which one)?

3
  • You should have declared those columns as timestamp instead of the dreaded epoch then you wouldn't have that problem now.
    – user1822
    Aug 25, 2022 at 18:57
  • but you are right: April, 28 is CEST and March 14 is CET, so the result is correct.
    – user1822
    Aug 25, 2022 at 19:03
  • @a_horse_with_no_name Not sure I understand. The contents of this column is external data: A float giving the epoch of the event. I used \copy to get the csv data into the table. How would I have to read the data such that it is more convenient to use? In my experience, it is a tradeoff between the ease of getting the data into the database and the manipulations afterwards.
    – gernot
    Aug 25, 2022 at 21:20

2 Answers 2

4

Austria has daylight savings time, so PostgreSQL will display timestamps in winter with a different UTC offset than timestamps in spring. to_timestamp converts an UTC epoch to a (time zone independent!) timestamp with time zone. Only when the timestamp with timezone is rendered as string, this is done according to the current setting of timezone.

All your timestamps are displayed in the same time zone: Europe/Vienna. This time zone has different offsets from UTC at different times of the year. If you want all timestamps shown with the same UTC offset, choose a timezone setting that has no daylight savings time, for example UTC or +02.

4
  • Thanks for your explanation. I understand that to_timestamp shows the timestamp with timezone, and apparently chooses the timezone depending on the timezone set and the winter/summer time at the time of the timestamp. I do not understand what you mean by Only when .... Isn't this what my example does, displaying the timestamps as strings? How would I have to reformulate the expressions such that both timestamps are shown with the same timezone?
    – gernot
    Aug 26, 2022 at 8:22
  • You understood correctly: the timestamps are rendered as strings, so timezone is respected. All timestamps are displayed in the same time zone! Aug 26, 2022 at 9:07
  • Please check the example that I give above: The timestamps are not displayed in the same time zone. This is exactly the point of my posting: How to display them in the same time zone, with the same offset?
    – gernot
    Aug 26, 2022 at 12:24
  • Oh yes, they are displayed in the same time zone. But they are displayed with different time zone offset. As I wrote, if you want the same offset for all, use a different timezone setting. Aug 26, 2022 at 13:17
1

to_timestamp displays an epoch according to the currently set time zone, with an appropriate offset. If the time zone happens to have a daylight saving time (DST), then the offset chosen depends on whether the epoch falls into the range of the DST or not. For the examples below, observe that on 2022-03-27 02:00:00+01, the time zone CET switched from standard time to DST.

=> set time zone 'CET';
=> select to_timestamp(1648342799) t,to_timestamp(1648342799+1) "t+1s";
           t            |          t+1s          
------------------------+------------------------
 2022-03-27 01:59:59+01 | 2022-03-27 03:00:00+02

To obtain identical offsets, either set the time zone to one without accompanying DST:

=> set time zone 'UTC';
=> select to_timestamp(1648342799) t,to_timestamp(1648342799+1) "t+1s";
           t            |          t+1s          
------------------------+------------------------
 2022-03-27 00:59:59+00 | 2022-03-27 01:00:00+00

or specify the time zone explicitly each time you display an epoch:

=> select to_timestamp(1648342799) at time zone 'CET' t,to_timestamp(1648342799+1) at time zone 'CET' "t+1s";
          t          |        t+1s         
---------------------+---------------------
 2022-03-27 01:59:59 | 2022-03-27 02:00:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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