Обсуждение: Bug in AT TIME ZONE contruct between EST and INTERVAL '-05:00' (resubmit now that I am a member of this list)
Bug in AT TIME ZONE contruct between EST and INTERVAL '-05:00' (resubmit now that I am a member of this list)
От
Joshua Moore-Oliva (by way of Joshua Moore-Oliva
Дата:
When selecting with the AT TIME ZONE or timezone function, the returned value is not always a timestamp. This is inconsistent with the documentation. To reproduce this problem, run these two queries. SELECT now() AT TIME ZONE 'EST' returns 2003-03-13 21:27:14.63401-05 SELECT now() AT TIME ZONE INTERVAL '-05:00' returns 1167 days 21:54:30.952135995 (Queries were run at different time, the problem is that it is returning an interval instead of a timestamp). When I attempt to cast the interval as a timestamp it gives me an error saying that it's not possible. I am pretty sure this is a bug. Following is supoprting dpcumentation from the manual. From the documentation In these expressions, the desired time zone can be specified either as a text string (e.g., 'PST') or as an interval (e.g., INTERVAL '-08:00'). Examples (supposing that TimeZone is PST8PDT): SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'MST'; Result: 2001-02-16 19:38:40-08 SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE 'MST'; Result: 2001-02-16 18:38:40 The first example takes a zone-less timestamp and interprets it as MST time (GMT-7) to produce a UTC timestamp, which is then rotated to PST (GMT-8) for display. The second example takes a timestamp specified in EST (GMT-5) and converts it to local time in MST (GMT-7). The function timezone(zone, timestamp) is equivalent to the SQL-compliant construct timestamp AT TIME ZONE zone. Josh.
In addition, I have upgraded to postgre 7.3.2 instead of postgre 7.3 and I get the exact same output except that years are included in the interval. It still returns an interval instead of a timestamp. josh. On March 13, 2003 10:12 pm, Joshua Moore-Oliva wrote: > When selecting with the AT TIME ZONE or timezone function, the returned > value is not always a timestamp. This is inconsistent with the > documentation. > > To reproduce this problem, run these two queries. > > SELECT now() AT TIME ZONE 'EST' > > returns 2003-03-13 21:27:14.63401-05 > > SELECT now() AT TIME ZONE INTERVAL '-05:00' > > returns 1167 days 21:54:30.952135995 > > (Queries were run at different time, the problem is that it is returning an > interval instead of a timestamp). > > When I attempt to cast the interval as a timestamp it gives me an error > saying that it's not possible. > > I am pretty sure this is a bug. Following is supoprting dpcumentation from > the manual. > > From the documentation > > In these expressions, the desired time zone can be specified either as a > text string (e.g., 'PST') or as an interval (e.g., INTERVAL '-08:00'). > > > Examples (supposing that TimeZone is PST8PDT): > > SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'MST'; > Result: 2001-02-16 19:38:40-08 > > SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE > 'MST'; Result: 2001-02-16 18:38:40 > > The first example takes a zone-less timestamp and interprets it as MST > time (GMT-7) to produce a UTC timestamp, which is then rotated to PST > (GMT-8) for display. The second example takes a timestamp specified in EST > (GMT-5) and converts it to local time in MST (GMT-7). > > > The function timezone(zone, timestamp) is equivalent to the SQL-compliant > construct timestamp AT TIME ZONE zone. > > Josh. > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html
A kind person on the pgsql-general list told me. "This appears to be a simple typo in the pg_proc entry for the function. The underlying C code thinks it is returning a timestamp without time zone, but that's not what the pg_proc entry has. You can fix this in an existing database by doing UPDATE pg_proc SET prorettype = 1114 WHERE prosrc = 'timestamptz_izone';" This fixed the problem for me. Josh. On March 13, 2003 10:12 pm, Joshua Moore-Oliva wrote: > When selecting with the AT TIME ZONE or timezone function, the returned > value is not always a timestamp. This is inconsistent with the > documentation. > > To reproduce this problem, run these two queries. > > SELECT now() AT TIME ZONE 'EST' > > returns 2003-03-13 21:27:14.63401-05 > > SELECT now() AT TIME ZONE INTERVAL '-05:00' > > returns 1167 days 21:54:30.952135995 > > (Queries were run at different time, the problem is that it is returning an > interval instead of a timestamp). > > When I attempt to cast the interval as a timestamp it gives me an error > saying that it's not possible. > > I am pretty sure this is a bug. Following is supoprting dpcumentation from > the manual. > > From the documentation > > In these expressions, the desired time zone can be specified either as a > text string (e.g., 'PST') or as an interval (e.g., INTERVAL '-08:00'). > > > Examples (supposing that TimeZone is PST8PDT): > > SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'MST'; > Result: 2001-02-16 19:38:40-08 > > SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE > 'MST'; Result: 2001-02-16 18:38:40 > > The first example takes a zone-less timestamp and interprets it as MST > time (GMT-7) to produce a UTC timestamp, which is then rotated to PST > (GMT-8) for display. The second example takes a timestamp specified in EST > (GMT-5) and converts it to local time in MST (GMT-7). > > > The function timezone(zone, timestamp) is equivalent to the SQL-compliant > construct timestamp AT TIME ZONE zone. > > Josh. > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html