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@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073