Note: This patch is intended for 8.1 (as was the original).
I believe the previous patch I submitted to convert Unix epoch to timestamptz contains a bug relating to its use of AT TIME ZONE. Please find attached a corrected patch diffed against HEAD, which includes documentation.
The original function was equivalent to
CREATE FUNCTION to_timestamp (DOUBLE PRECISION)
RETURNS timestamptz
LANGUAGE SQL AS '
select (
(\'epoch\'::timestamptz + $1 * \'1 second\'::interval)
at time zone \'UTC\'
)
';The AT TIME ZONE 'UTC' removes the time zone from the timestamptz, returning timestamp. However, the function is declared to return timestamptz. The original patch appeared to work, but creating this equivalent function fails as it doesn't return the declared datatype.
The corrected function restores the time zone with an additional AT TIME ZONE 'UTC':
CREATE FUNCTION to_timestamp (double precision)
returns timestamptz
language sql as '
select (
(\'epoch\'::timestamptz + $1 * \'1 second\'::interval)
at time zone \'UTC\'
) at time zone \'UTC\'
';
Michael Glaesemann grzm myrealbox com
to_timestamp-20041212.diff
Description: application/text
---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
