pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedInconsistent 'at time zone' conversion
5+ messages / 3 participants
[nested] [flat]
* Inconsistent 'at time zone' conversion
@ 2023-02-19 22:36 Umut TEKİN <umuttechin@gmail.com>
2023-02-19 23:46 ` Re: Inconsistent 'at time zone' conversion David G. Johnston <david.g.johnston@gmail.com>
2023-02-19 23:49 ` Re: Inconsistent 'at time zone' conversion Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 5+ messages in thread
From: Umut TEKİN @ 2023-02-19 22:36 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org
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%20ZON...,
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Inconsistent 'at time zone' conversion
2023-02-19 22:36 Inconsistent 'at time zone' conversion Umut TEKİN <umuttechin@gmail.com>
@ 2023-02-19 23:46 ` David G. Johnston <david.g.johnston@gmail.com>
1 sibling, 0 replies; 5+ messages in thread
From: David G. Johnston @ 2023-02-19 23:46 UTC (permalink / raw)
To: Umut TEKİN <umuttechin@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org
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+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
>
> 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'.
>
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.
https://www.postgresql.org/docs/current/datetime-posix-timezone-specs.html
David J.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Inconsistent 'at time zone' conversion
2023-02-19 22:36 Inconsistent 'at time zone' conversion Umut TEKİN <umuttechin@gmail.com>
@ 2023-02-19 23:49 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-02-20 12:54 ` Re: Inconsistent 'at time zone' conversion Umut TEKİN <umuttechin@gmail.com>
1 sibling, 1 reply; 5+ messages in thread
From: Tom Lane @ 2023-02-19 23:49 UTC (permalink / raw)
To: Umut TEKİN <umuttechin@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org
=?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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Inconsistent 'at time zone' conversion
2023-02-19 22:36 Inconsistent 'at time zone' conversion Umut TEKİN <umuttechin@gmail.com>
2023-02-19 23:49 ` Re: Inconsistent 'at time zone' conversion Tom Lane <tgl@sss.pgh.pa.us>
@ 2023-02-20 12:54 ` Umut TEKİN <umuttechin@gmail.com>
2023-02-20 15:05 ` Re: Inconsistent 'at time zone' conversion Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: Umut TEKİN @ 2023-02-20 12:54 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; david.g.johnston@gmail.com; +Cc: pgsql-bugs@lists.postgresql.org
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
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:
> =?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
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Inconsistent 'at time zone' conversion
2023-02-19 22:36 Inconsistent 'at time zone' conversion Umut TEKİN <umuttechin@gmail.com>
2023-02-19 23:49 ` Re: Inconsistent 'at time zone' conversion Tom Lane <tgl@sss.pgh.pa.us>
2023-02-20 12:54 ` Re: Inconsistent 'at time zone' conversion Umut TEKİN <umuttechin@gmail.com>
@ 2023-02-20 15:05 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2023-02-20 15:05 UTC (permalink / raw)
To: Umut TEKİN <umuttechin@gmail.com>; +Cc: david.g.johnston@gmail.com; pgsql-bugs@lists.postgresql.org
=?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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2023-02-20 15:05 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-02-19 22:36 Inconsistent 'at time zone' conversion Umut TEKİN <umuttechin@gmail.com>
2023-02-19 23:46 ` David G. Johnston <david.g.johnston@gmail.com>
2023-02-19 23:49 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-02-20 12:54 ` Umut TEKİN <umuttechin@gmail.com>
2023-02-20 15:05 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox