This query:

cte AS ( SELECT 0 n
         UNION ALL
         SELECT n + 1 FROM cte WHERE n < 20)
SELECT n, ('2020-04-13 00:00:00 Europe/Berlin'::timestamptz - make_interval(days => n)) AT TIME ZONE 'Europe/Berlin'
FROM cte

On my machine it gives this result:


Note that the time switches from 00:00 to 23:00 on the day of the DST switch.

On dbfiddle.uk (Link) the query gives this result:


Note that it shows 00:00 for all days, there is no change on the day of the DST switch.

And then on sqlfiddle.com (Link) the result is:


Here we have the switch again, but something else is strange here: The time zone of the result is given as Z (UTC) even though everything before was in time zone Europe/Berlin.

  • 1
    sqlfiddle.com is not running a pure community PostgreSQL. It is their own compilation and has been whacked around a bit. – jjanes Apr 24 at 23:25

sqlfiddle.com might interpret returned timestamps as having a timezone.

But PostgreSQL does not store a timezone along with a timestamp. timestamp with timezone fields are internally stored in UTC: https://www.postgresql.org/docs/11/datatype-datetime.html#DATATYPE-TIMEZONES

A timezone is not returned in this query either.

So this behavior of sqlfiddle.com is misleading.

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.