Re: Undocumented AT TIME ZONE INTERVAL syntax

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Corey Huinker <corey(dot)huinker(at)gmail(dot)com>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: Undocumented AT TIME ZONE INTERVAL syntax
Date: 2021-11-12 22:15:22
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

Corey Huinker <corey(dot)huinker(at)gmail(dot)com> writes:
> Attached is an attempt at an explanation of the edge cases I was
> encountering, as well as some examples. If nothing else, the examples will
> draw eyes and searches to the explanations that were already there.

I looked this over and have a few thoughts:

* I don't think your explanation of the behavior of colon-separated
times is quite correct; for example, it doesn't correctly describe

regression=# select INTERVAL '2:03:04' minute to second;
(1 row)

I think the actual rule is that hh:mm:ss is always interpreted that
way regardless of the typmod (though we may then drop low-order
fields if the typmod says to). Two colon-separated numbers are
interpreted as hh:mm by default, but as mm:ss if the typmod is
exactly "minute to second". (This might work better in a separate
para; the one you've modified here is mostly about what we do with
unmarked quantities, but the use of colons makes these numbers
not unmarked.)

* I'm not sure I would bother with examples for half-broken formats
like "2:". People who really want to know about that can experiment,
while for the rest of us it seems like adding confusion.

* In the same vein, I think your 0002 example adds way more confusion
than illumination. Maybe better would be a less contrived offset,

# SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40+00' AT TIME ZONE INTERVAL '3:00:00';
2001-02-16 23:38:40
(1 row)

which could be put after the second example and glossed as "The third
example rotates a timestamp specified in UTC to the zone three hours
east of Greenwich, using a constant interval as the time zone

regards, tom lane

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Ilya Anfimov 2021-11-12 22:47:17 Re: Should AT TIME ZONE be volatile?
Previous Message Nikolay Samokhvalov 2021-11-12 21:14:18 Re: Commitfest 2021-11 Patch Triage - Part 2