Craig Ringer wrote:
> I'm mildly thrown by this:
>
> regress=> SELECT TIME '04:00' AT TIME ZONE '01:00';
> timezone
> -------------
> 19:00:00-01
> (1 row)
>
> regress=> SELECT TIME '04:00' AT TIME ZONE (INTERVAL '01:00');
> timezone
> -------------
> 21:00:00+01
> (1 row)
>
> regress=> SELECT TIME '04:00' AT TIME ZONE (TEXT '01:00');
> timezone
> -------------
> 19:00:00-01
> (1 row)
>
>
> and was wondering if anyone knows why the sense of the offset is
> reversed for typed intervals vs bare literal or text. Is this another
> one of the issues caused by the various standards' disagreements about +
> vs - time offsets?
The function that implements the "text" case has this comment:
/* timestamp_zone()
* Encode timestamp type with specified time zone.
* This function is just timestamp2timestamptz() except instead of
* shifting to the global timezone, we shift to the specified timezone.
* This is different from the other AT TIME ZONE cases because instead
* of shifting to a _to_ a new time zone, it sets the time to _be_ the
* specified timezone.
*/
The comment was introduced in commits 5ddeffb676e6bb64b82fc98576f3fe54f8671527
and 3dbbbbf8e98329e1eea9920436defc64af3594d3, there is a discussion
on -patches:
http://www.postgresql.org/message-id/slrnde2134.2k2r.andrew+nonews@trinity.supernews.net
I can't say if that's correct though.
Yours,
Laurenz Albe