Inconsistent 'at time zone' conversion - Mailing list pgsql-bugs
| From | Umut TEKİN |
|---|---|
| Subject | Inconsistent 'at time zone' conversion |
| Date | |
| Msg-id | CAPZcZRmcTnGPABC8o+5hwLz2wN0uHK=hG+Xj=swVvDS8i5Dekg@mail.gmail.com Whole thread Raw |
| Responses |
Re: Inconsistent 'at time zone' conversion
Re: Inconsistent 'at time zone' conversion |
| List | pgsql-bugs |
Hi,
2. select now()::timestamp with time zone at time zone 'America/Denver';
I think I found a bug about time zones.
Here are the details:
First of all, I am using PostgreSQL 15 on my Rocky Linux Rocky-8.7(on docker) and my "TimeZone" setting is set to default value, "GMT".
According to the official documentation I was trying out conversion using "AT TIME ZONE".
I tried out;
-----------------------------------Pattern is "Command -> Ouput"-----------------------------------
1. select now();
2023-02-19 21:52:12.537849+00
2023-02-19 14:52:12.5383
3. select now()::timestamp with time zone at time zone 'UTC-7';
2023-02-20 04:52:12.538804
4. select TIMESTAMP WITH TIME ZONE '2004-10-19 10:23:54+03' at time zone 'America/Denver';
2004-10-19 01:23:54
5. select TIMESTAMP WITH TIME ZONE '2004-10-19 10:23:54+03' at time zone 'UTC-7';
2004-10-19 14:23:54
6. select (now()::timestamp with time zone - interval '18 years 4 months 14 hours 29 minutes');
2004-10-19 07:23:12.539225+01
7. select (now()::timestamp with time zone - interval '18 years 4 months 14 hours 29 minutes') AT TIME ZONE 'America/Denver';
2004-10-19 00:23:12.539496
8. select (now()::timestamp with time zone - interval '18 years 4 months 14 hours 29 minutes') AT TIME ZONE 'UTC-7';
2004-10-19 13:23:12.539624
In America/Denver, UTC-7 is using as time zone that is why 1st and 2nd queries confirm each other, but 3rd query completely disapproves first two queries. Instead of '2023-02-19 14:52:12.538804', it shows '2023-02-20 04:52:12.538804'. Probably, it double minus sign like "- (-7) = +7" and then adds to actual time/timestamp. Yet, this is not the only thing that is inconsistent. When I use the timestamp from official documentaion(https://www.postgresql.org/docs/current/datatype-datetime.html#:~:text=TIMESTAMP%20WITH%20TIME%20ZONE%20%272004%2D10%2D19%2010%3A23%3A54%2B02%27, I only change timezone from +02 to +03 to test) with "at time zone" conversion, it was quite confusing. Neither the output of 4th and 5h queries are correct nor they confirm each other. The output should be as the output of 7th query. Between GMT+03 and GMT+07 there are 10 hours and it leads us to '2004-10-19 10:23:54+03' - 10 hours -> '2004-10-19 00:23:54+03', but it is not for the queries 4th and 5th.
The time when I executed this queries was '2023-02-19 21:52:12.537849+00', so in order to use the same time with documentation I used "interval" as in the query the 6th. Then, I used the conversion, and I got the correct value from query 7th. However, the output of the 8th query is also wrong. For this specific timestamps I also checked DST for the Denver and the output of 7th is correct, but there is inconsistency between the output. Especially, interpreting the "at time zone '+/- xx'" causing inconsistency.
In addition, this is not region or timezone specific. You can also check:
select now()::timestamp with time zone at time zone 'IST';
select now()::timestamp with time zone at time zone 'IST';
select now()::timestamp with time zone at time zone 'UTC+2';
select TIMESTAMP WITH TIME ZONE '2004-10-19 10:23:54+03' at time zone 'IST';
select TIMESTAMP WITH TIME ZONE '2004-10-19 10:23:54+03' at time zone 'UTC+2';
select TIMESTAMP WITH TIME ZONE '2004-10-19 10:23:54+03' at time zone 'IST';
select TIMESTAMP WITH TIME ZONE '2004-10-19 10:23:54+03' at time zone 'UTC+2';
Could you please check?
Thanks!
Umut TEKIN
pgsql-bugs by date: