Thread: AT TIME ZONE and interval arguments

Hi all

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?

--
 Craig Ringer                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

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

Craig Ringer <craig@2ndquadrant.com> writes:
> I'm mildly thrown by this:
> regress=> SELECT TIME '04:00' AT TIME ZONE '01:00';
> [ zone is taken as GMT-1 ]
> regress=> SELECT TIME '04:00' AT TIME ZONE (INTERVAL '01:00');
> [ zone is taken as GMT+1 ]

> and was wondering if anyone knows why the sense of the offset is
> reversed for typed intervals vs bare literal or text.

Well, it's "interval" vs "not interval", and you'll get the same
behavior from SET TIME ZONE:

regression=# set time zone '01:00';
SET
regression=# select now();
              now
-------------------------------
 2013-01-31 14:57:44.707581-01
(1 row)

regression=# set time zone interval '01:00';
SET
regression=# select now();
              now
-------------------------------
 2013-01-31 16:57:54.707828+01
(1 row)

so at least AT TIME ZONE isn't off in the weeds by itself.

The sign interpretation in the SET TIME ZONE INTERVAL case is dictated
by the SQL standard.  In the non-interval case, we treat the string as
a time zone name, and I think it must be reading it as a POSIX timezone
spec since there is certainly no entry for '01:00' in the Olson tz
database.  So yeah, this is another facet of the SQL-vs-POSIX conflict.

BTW, I happened to notice while looking at timetz_izone() that it does
this:

    if (zone->month != 0)
        ereport(ERROR,
                (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
                 errmsg("\"interval\" time zone \"%s\" not valid",
                        DatumGetCString(DirectFunctionCall1(interval_out,
                                                  PointerGetDatum(zone))))));

It looks to me like the intent is to reject day-or-more intervals,
as is done in variable.c's check_timezone().  Probably this got missed
when we added the separate day field to intervals.  Will fix ...

            regards, tom lane