Undocumented AT TIME ZONE INTERVAL syntax

From: Corey Huinker <corey(dot)huinker(at)gmail(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Undocumented AT TIME ZONE INTERVAL syntax
Date: 2021-09-19 01:28:49
Message-ID: CADkLM=cP+gVDwbQ9K-r9=6Nd2ThiareqhZYo71ZEK6CK1n6mrg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

In reviewing Paul's application period patch, I noticed some very curious
syntax in the test cases. I learned that Paul is equally confused by it,
and has asked about it in his PgCon 2020 presentation

> SELECT '2018-03-04' AT TIME ZONE INTERVAL '2' HOUR TO MINUTE;
timezone
---------------------
2018-03-04 05:02:00
(1 row)

Searching around, I found several instances of this syntax being used
[1][2][3], but with one important clarifying difference: the expected
syntax was

> SELECT '2018-03-04' AT TIME ZONE INTERVAL '2:00' HOUR TO MINUTE;
timezone
---------------------
2018-03-04 07:00:00
(1 row)

Now I understand that the user probably meant to do this:

# SELECT '2018-03-04' AT TIME ZONE INTERVAL '2' HOUR;
timezone
---------------------
2018-03-04 07:00:00
(1 row)

But none of this is in our own documentation.

Before I write a patch to add this to the documentation, I'm curious what
level of sloppiness we should tolerate in the interval calculation. Should
we enforce the time string to actually conform to the format laid out in
the X TO Y spec? If we don't require that, is it correct to say that the
values will be filled from order of least significance to greatest?

[1]
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/DataTypes/Date-Time/TIMESTAMPATTIMEZONE.htm
[2]
https://docs.teradata.com/r/kmuOwjp1zEYg98JsB8fu_A/aWY6mGNJ5CYJlSDrvgDQag
[3]
https://community.snowflake.com/s/question/0D50Z00009AqIaSSAV/is-it-possible-to-add-an-interval-of-5-hours-to-the-session-timezone-

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message DEVOPS_WwIT 2021-09-19 01:43:22 Re: So, about that cast-to-typmod-minus-one business
Previous Message Corey Huinker 2021-09-19 00:46:16 Re: SQL:2011 application time