Thread: Unixtime (epoch) into timestamp?

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.
I looked into the interactive docs, but did not see a way to do this.

Any suggestions?

- Ericson Smith
eric@did-it.com

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

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

Ericson Smith <eric@did-it.com> writes:
> 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';

Better make that
     SELECT INTO my_ret "timestamptz"(my_epoch);
else it will break in 7.3 --- "timestamp" is going to start meaning
"timestamp without time zone", per SQL spec.  Likewise my_ret had
better be explicitly declared timestamptz or timestamp with time zone.
Otherwise looks good.

            regards, tom lane