Re: BUG #17240: at time zone ... ; wrong result - Mailing list pgsql-bugs
| From | Marek Läll |
|---|---|
| Subject | Re: BUG #17240: |
| Date | |
| Msg-id | CADDPzFTa7ipw157Ep7rx2a=grWN71xsfXA4-=X31LzGEuv4_fA@mail.gmail.com Whole thread Raw |
| In response to |
Re: BUG #17240: |
| Responses |
Re: BUG #17240: |
| List | pgsql-bugs |
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
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@sss.pgh.pa.us>) kirjutas kuupäeval N, 21. oktoober 2021 kell 16:41:
PG Bug reporting form <noreply@postgresql.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
pgsql-bugs by date: