Re: Inconsistent 'at time zone' conversion

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(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> =?UTF-8?Q?Umut_TEK=C4=B0N?= <umuttechin(at)gmail(dot)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
>