Re: BUG #17240: <timestamptz> at time zone ... ; wrong result

Hi!

Thank you for your response, Tom.

I have multiple questions/comments but let's start one-by-one.

POSIX string (syntax) is defined as:
--> stdoffset[dst[offset][,start-date[/time],end-date[/time]]]

The std string specifies the name of the time zone.
It must be three or more characters long and must not contain a leading
colon, embedded digits, commas, nor plus and minus signs.
There is no space character separating the time zone name from the offset,
so these restrictions are necessary to parse the specification correctly.

For "Pacific Time (Canada & US)" aka "America/Los_Angeles" the POSIX
version looks like: "PST8PDT,M3.2.0,M11.1.0"

Here are results of experiment:

$ export TZ="PST8PDT,M3.2.0,M11.1.0" ; date --iso-8601=seconds
2021-10-24T12:10:51-07:00 <-- CORRECT

$ export TZ="UTC" ; date --iso-8601=seconds
2021-10-24T19:11:32+00:00 <-- CORRECT

$ export TZ="-07:00" ; date --iso-8601=seconds
2021-10-24T19:11:42+00:00 <-- TZ is IGNORED, because "-07:00" is not
valid POSIX syntax

$ export TZ="-0700" ; date --iso-8601=seconds
2021-10-24T19:11:46+00:00 <-- TZ is IGNORED, because "-0700" is not valid
POSIX syntax

$ export TZ="-07" ; date --iso-8601=seconds
2021-10-24T19:55:08+00:00 <-- TZ is IGNORED, because "-07" is not valid
POSIX syntax

$ export TZ="-07RandomMeaninglessString" ; date --iso-8601=seconds
2021-10-24T19:14:55+00:00 <-- TZ is IGNORED, because
"-07RandomMeaninglessString" is not valid POSIX syntax

$ uname -a
Linux 5.10.0-8-amd64 #1 SMP Debian 5.10.46-4 (2021-08-03) x86_64 GNU/Linux

Back to POSTGRES case:
> postgres> select timestamptz'2021-10-01 07:00:00 UTC' at time zone
'-07:00' as revers_res2;
> 2021-10-01 14:00:00

The value '-07:00' is not a valid POSIX value but ... Postgres performs the
following (as concept):
1) first: reads in the string '-07:00'
2) then: ignores the fact that string '-07:00' is invalid POSIX value
3) then: decides to follow "POSIX sign convention" rules (still ignoring
previous fact)
4) and then: applies "POSIX sign convention" rules using invalid POSIX
value as input?

Regards
Marek

Kontakt Tom Lane (<tgl(at)sss(dot)pgh(dot)pa(dot)us>) kirjutas kuupäeval N, 21. oktoober
2021 kell 16:41:

> PG Bug reporting form <noreply(at)postgresql(dot)org> writes:
> > # select now()
> > , now() at time zone 'America/Los_Angeles' as correct
> > , now() at time zone '-07:00:00' as wrong;
>
> Unfortunately, the pure-numeric syntax for time zone names follows the
> POSIX sign convention, which is opposite to the ISO convention used
> in pg_timezone_names.utc_offset (and in most other places in Postgres).
> So "at time zone '+07:00:00'" is what you needed to write to duplicate
> the 'America/Los_Angeles' result. See
>
>
> https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-TIMEZONES
>
> Now, if you'd done this:
>
> select ... now() at time zone interval '-07:00:00' as fine
>
> you'd have gotten the ISO sign interpretation. But an undecorated
> literal string defaults to being of type text, meaning you get
> the time-zone-name logic path.
>
> The great thing about standards is there are so many to choose from :-(
>
> regards, tom lane
>
>
>