Thread: Inconsistent 'at time zone' conversion
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
On Sun, Feb 19, 2023 at 4:10 PM Umut TEKİN <umuttechin@gmail.com> wrote:
I think I found a bug about time zones.
Nope, the observed behavior is documented.
1. select now();2023-02-19 21:52:12.537849+002. select now()::timestamp with time zone at time zone 'America/Denver';2023-02-19 14:52:12.53833. select now()::timestamp with time zone at time zone 'UTC-7';2023-02-20 04:52:12.538804In 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'.
Unfortunately the actual +/- direction convention in use here is not the one you are expecting. It is defined to be the reverse of what you've said, and so you get the observed behavior.
David J.
=?UTF-8?Q?Umut_TEK=C4=B0N?= <umuttechin@gmail.com> writes: > I think I found a bug about time zones. You're just confused about the signs of timezone offsets. "UTC-7" is read as POSIX notation so that it means 7 hours east of Greenwich, not west where Denver is. See https://www.postgresql.org/docs/current/datetime-posix-timezone-specs.html regards, tom lane
Hi,
Thank you for your answers David G. Johnston and Tom Land. That is much more clear now.
Yet, there is one tiny thing left:
1. select TIMESTAMP WITH TIME ZONE '2004-10-19 10:23:54+03' at time zone 'America/Denver';
2004-10-19 01:23:54
2. select TIMESTAMP WITH TIME ZONE '2004-10-19 10:23:54+03' at time zone 'UTC+7';
2004-10-19 00:23:54
3. select TIMESTAMP WITH TIME ZONE '2004-10-19 10:23:54+03' at time zone 'Europe/Moscow';
2004-10-19 11:23:54
2004-10-19 11:23:54
4. select TIMESTAMP WITH TIME ZONE '2004-10-19 10:23:54+03' at time zone 'UTC-3';
2004-10-19 10:23:54
The outputs of the 1st and the 2nd queries should be the same because "+/-" works opposite of ISO - 8601. Same thing should apply to query 3rd and 4th.
When I try following queries they give exactly the same output.
select now()::timestamp with time zone at time zone 'America/Denver';
select now()::timestamp with time zone at time zone 'UTC+7';
So, it should be related to the date. Yet, as you can see '2004-10-19 10:23:54+03' is nearly the same time of the year 18 years and 4 months ago. So, the same DST rules should apply, but it does not. There is 1 hour difference between query 1st and 2nd, also query 3rd and 4th.
In order to check DST for the specified timestamp: https://www.timeanddate.com/worldclock/converter.html?iso=20040219T072300&p1=tz_gmt&p2=75
What could be the reason for this?
Thanks!
Umut Tekin
On Mon, Feb 20, 2023 at 12:49 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Umut TEKİN <umuttechin@gmail.com> writes:
> I think I found a bug about time zones.
You're just confused about the signs of timezone offsets.
"UTC-7" is read as POSIX notation so that it means 7 hours
east of Greenwich, not west where Denver is. See
https://www.postgresql.org/docs/current/datetime-posix-timezone-specs.html
regards, tom lane
=?UTF-8?Q?Umut_TEK=C4=B0N?= <umuttechin@gmail.com> writes:
> So, it should be related to the date. Yet, as you can see '2004-10-19
> 10:23:54+03' is nearly the same time of the year 18 years and 4 months ago.
> So, the same DST rules should apply, but it does not.
It's folly to make such an assertion without having checked tzdb ;-).
DST laws change from time to time.
A bit of experimentation shows that Postgres does think both those
zones were observing DST on 2004-10-19, and I would be quite surprised
if it's wrong about that. (Even if it's wrong, that would be tzdb's
bug not ours.)
regards, tom lane