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
