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

I have modified your original patch and applied it. Tom mentioned to me
privately that none of the AT TIME ZONE clauses is required, probably
because epoch is already UTC, and adding seconds to it just keeps it
UTC, then it is converted to your local timezone for display. I also
found your patch was lacking two _null_ columns that are now needed
because of pg_proc column additions.

Testing shows it works:

$ date '+%s'
1118333328

test=> select to_timestamp(1118333328);
to_timestamp
------------------------
2005-06-09 12:08:48-04
(1 row)

test=> select current_timestamp;
timestamptz
-------------------------------
2005-06-09 12:09:01.746045-04
(1 row)

Applied patch attached.

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

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