Re: Unixtime (epoch) into timestamp?

Thanks Tom,

Just added this handy little function to our catalog based on that
answer:

CREATE FUNCTION "fn_date"(int4) RETURNS timestamp with time zone AS
'DECLARE
my_epoch ALIAS FOR $1;
my_ret timestamp;

BEGIN
   SELECT INTO my_ret "timestamp"(my_epoch);
   RETURN my_ret;
END;' LANGUAGE 'plpgsql';

Thanks a bunch
- Ericson
eric@did-it.com

On Thu, 2002-09-26 at 11:07, Tom Lane wrote:
> Ericson Smith <eric@did-it.com> writes:
> > We mostly use unix times in our system because of the kind of
> > applications that we have. Now we need to convert that into a timestamp.
>
> The officially supported conversion methods are like this:
>
> test72=# select extract(epoch from now());
>     date_part
> ------------------
>  1033052570.73262
> (1 row)
>
> test72=# select 'epoch'::timestamptz + interval '1033052570.73262 seconds';
>            ?column?
> ------------------------------
>  2002-09-26 11:02:50.73262-04
> (1 row)
>
> But I tend to cheat on the latter.  You can cast from int4 to abstime,
> and the latter is really a time_t, so:
>
> test72=# select 1033052570::int4::abstime;
>         abstime
> ------------------------
>  2002-09-26 11:02:50-04
> (1 row)
>
> And of course you can cast from abstime to timestamp.  This will
> probably break in 2038 ...
>
>             regards, tom lane