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(at)did-it(dot)com

On Thu, 2002-09-26 at 11:07, Tom Lane wrote:
> Ericson Smith <eric(at)did-it(dot)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