Bug in AT TIME ZONE contruct between EST and INTERVAL '-05:00' (resubmit now that I am a member of this list)
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.
Show quoted text
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-08SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE
'MST'; Result: 2001-02-16 18:38:40The 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?
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.
Show quoted text
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-08SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE
'MST'; Result: 2001-02-16 18:38:40The 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?