Re: (8.1) to_timestamp correction (epoch to timestamptz)

This has been saved for the 8.1 release:

http:/momjian.postgresql.org/cgi-bin/pgpatches2

---------------------------------------------------------------------------

Michael Glaesemann wrote:
> 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
>

[ Attachment, skipping... ]

>

>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073