Re: AT TIME ZONE and interval arguments

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