PostgreSQL `at timezone` unexpected behaviour

Question

When running the following queries, the expected result would be the same (2023-01-16 16:39:24.824000) as the current timezone in Europe/Tirana is CEST. However, the results I receive are as shown:

select '2023-01-16 14:39:24.824000 +00:00' at time zone 'Europe/Tirane';
select '2023-01-16 14:39:24.824000 +00:00' at time zone 'CEST';

2023-01-16 15:39:24.824000
2023-01-16 16:39:24.824000

Meaning that Europe/Tirana does not return the correct result. Am I missing something? Why is this behaviour happening? Thank you in advance!

I tried using ::timestamptz and some other suggestions I found but the result remained unchanged.

SelVazi · Accepted Answer · 2023-05-09 09:49:43Z

1

The result is correct because the Europe/Tirane time zone was switched from CET (UTC+1) to CEST (UTC+2) on 2023-03-26.

2023    dim 26 mar, 02 h 00 CET → CEST  +1 hour (DST start) UTC+2h
        dim 29 oct, 03 h 00 CEST → CET  -1 hour (DST end)   UTC+1h

so the result of : select '2023-01-16 14:39:24.824000 +00:00' at time zone 'Europe/Tirane';

should be 2023-01-16 15:39:24.824 , because 2023-01-16 is before 2023-03-26

answered May 9, 2023 at 9:49

SelVazi

16.2k2 gold badges19 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

vshtishi Over a year ago

Totally overlooked that, thank you!

Collectives™ on Stack Overflow

PostgreSQL `at timezone` unexpected behaviour

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related