ISO8601 vs POSIX offset clarification
Hi,
the documentation around how numeric offsets are parsed from strings is a
bit confusing, are they supposed to be treated as ISO8601 or POSIX ?
e.g.
select '2017-12-01 11:00:00 +11:00'::timestamp with time zone at time zone
'+11:00';
timezone
---------------------
2017-11-30 13:00:00
select '2017-12-01 11:00:00 -11:00'::timestamp with time zone at time zone
'+11:00';
timezone
---------------------
2017-12-01 11:00:00
The Table 8-12. Time Zone Input section at
https://www.postgresql.org/docs/9.3/static/datatype-datetime.html seems to
imply that numeric offsets would be treated as ISO8601. It's all a big
confusing and would appreciate some clarification or pointer to
documentation.
Thanks
Bharanee Rathna <deepfryed@gmail.com> writes:
the documentation around how numeric offsets are parsed from strings is a
bit confusing, are they supposed to be treated as ISO8601 or POSIX ?
Our documentation about this says clearly that Postgres considers offsets
to be ISO (positive-east-of-Greenwich) everywhere except in POSIX-style
time zone names.
The Table 8-12. Time Zone Input section at
https://www.postgresql.org/docs/9.3/static/datatype-datetime.html seems to
imply that numeric offsets would be treated as ISO8601.
How do you read an entry such as
-8:00 | ISO-8601 offset for PST
as being in any way vague about which convention the "-8" is read in?
regards, tom lane
Sorry I didn't mean for it to come out as a complaint, just that I am
confused since the result of the SQL query was not what I expected. I
expected +11:00 to be 11 hours east of UTC which wasn't the case.
On 4 December 2017 at 13:55, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Show quoted text
Bharanee Rathna <deepfryed@gmail.com> writes:
the documentation around how numeric offsets are parsed from strings is a
bit confusing, are they supposed to be treated as ISO8601 or POSIX ?Our documentation about this says clearly that Postgres considers offsets
to be ISO (positive-east-of-Greenwich) everywhere except in POSIX-style
time zone names.The Table 8-12. Time Zone Input section at
https://www.postgresql.org/docs/9.3/static/datatype-datetime.html seemsto
imply that numeric offsets would be treated as ISO8601.
How do you read an entry such as
-8:00 | ISO-8601 offset for PST
as being in any way vague about which convention the "-8" is read in?
regards, tom lane
To be more specific, I expected the output of both these queries to be the
same.
# select '2017-12-01 11:00:00 +11:00'::timestamp with time zone at time
zone '+11:00';
timezone
---------------------
2017-11-30 13:00:00
# select '2017-12-01 11:00:00 +11:00'::timestamp with time zone at time
zone 'Australia/Melbourne';
timezone
---------------------
2017-12-01 11:00:00
Cheers
On 4 December 2017 at 13:59, Bharanee Rathna <deepfryed@gmail.com> wrote:
Show quoted text
Sorry I didn't mean for it to come out as a complaint, just that I am
confused since the result of the SQL query was not what I expected. I
expected +11:00 to be 11 hours east of UTC which wasn't the case.On 4 December 2017 at 13:55, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Bharanee Rathna <deepfryed@gmail.com> writes:
the documentation around how numeric offsets are parsed from strings is
a
bit confusing, are they supposed to be treated as ISO8601 or POSIX ?
Our documentation about this says clearly that Postgres considers offsets
to be ISO (positive-east-of-Greenwich) everywhere except in POSIX-style
time zone names.The Table 8-12. Time Zone Input section at
https://www.postgresql.org/docs/9.3/static/datatype-datetime.htmlseems to
imply that numeric offsets would be treated as ISO8601.
How do you read an entry such as
-8:00 | ISO-8601 offset for PST
as being in any way vague about which convention the "-8" is read in?
regards, tom lane
On Mon, 2017-12-04 at 14:03 +1100, Bharanee Rathna wrote:
To be more specific, I expected the output of both these queries to
be the same.# select '2017-12-01 11:00:00 +11:00'::timestamp with time zone at
time zone '+11:00';
timezone
---------------------
2017-11-30 13:00:00# select '2017-12-01 11:00:00 +11:00'::timestamp with time zone at
time zone 'Australia/Melbourne';
timezone
---------------------
2017-12-01 11:00:00Cheers
select '2017-12-01 11:00:00 +11:00'::timestamp with time zone at time
zone INTERVAL '+11:00';
Result:-
2017-12-01 11:00:00.0
You need the INTERVAL keyword when using a numeric value instead of a
time zone name. It's in the doco.
The parser ought to throw an error, but it doesn't.
Bharanee Rathna <deepfryed@gmail.com> writes:
To be more specific, I expected the output of both these queries to be the
same.
# select '2017-12-01 11:00:00 +11:00'::timestamp with time zone at time
zone '+11:00';
# select '2017-12-01 11:00:00 +11:00'::timestamp with time zone at time
zone 'Australia/Melbourne';
The +11 in your timestamp input value is per ISO convention, but the
argument of AT TIME ZONE is a zone name, so it follows the POSIX
convention if it's numeric.
regards, tom lane