Inconsistent 'at time zone' conversion

Поиск
Список
Период
Сортировка
От Umut TEKİN
Тема Inconsistent 'at time zone' conversion
Дата
Msg-id CAPZcZRmcTnGPABC8o+5hwLz2wN0uHK=hG+Xj=swVvDS8i5Dekg@mail.gmail.com
обсуждение исходный текст
Ответы Re: Inconsistent 'at time zone' conversion  ("David G. Johnston" <david.g.johnston@gmail.com>)
Re: Inconsistent 'at time zone' conversion  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-bugs
Дерево обсуждения
Hi,

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

2. select now()::timestamp with time zone  at time zone 'America/Denver';
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 '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 по дате отправления:

Предыдущее
От: "Stephen L. De Rudder"
Дата:
Сообщение: PostgreSQL Windows Install Log causes errorDump in log
Следующее
От: Tom Lane
Дата:
Сообщение: Re: BUG #17795: Erroneous parsing of floating-poing components in DecodeISO8601Interval()