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
