Craig Ringer <cr...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general