Thread: converting an epoch to a timestamp

How does one go about converting a UNIX epoch (ie. int4) to a timestamp?

These don't work:

("datetime" is the int4 field)

extract(timestamp from epoch datetime)
extract(timestamp from datetime)

I'm using 7.1.3, but am also interested in 7.2 solutions.  And if it isn't
part of extract, shouldn't it be?

Chris

"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
> How does one go about converting a UNIX epoch (ie. int4) to a timestamp?

I tend to rely on the binary equivalence between int4 and abstime:

regression=# select 1010626671::int4::abstime::timestamp;     timestamptz
------------------------2002-01-09 20:37:51-05
(1 row)

which can be written in a less ugly-looking though equivalent fashion:

regression=# select "timestamptz"(1010626671);     timestamp
---------------------2002-01-09 20:37:51-05
(1 row)

(In 7.1.3 you'd just write "timestamp" without the "tz".  Note that the
quotes are not optional in 7.2.)

However, Thomas keeps threatening to remove type abstime.  There are
more socially acceptable ways of doing it, eg

regression=# select 'epoch'::timestamptz + '1010626671 sec'::interval;       ?column?
------------------------2002-01-09 20:37:51-05
(1 row)

but this way does not work well when the integer in question is already
in the database, rather than being plugged into a literal query string
by an application.  I think some people have recommended

regression=# select 'epoch'::timestamptz + "interval"(1010626671::int4);       ?column?
------------------------2002-01-17 20:37:51-05
(1 row)

but as far as I can see that's just an extra runtime operation without
any redeeming social value, because when you look under the hood at the
interval coercion, you find out that it's relying on binary equivalence
between int4 and reltime ... so this too will break if we get rid of the
obsolete time types.

Personally I'd like to see an officially supported int4-to-timestamp
(or perhaps better double-to-timestamp, to be Y2038-compliant and
fractional-second-capable) conversion function.  Getting from Unix
timestamp to Postgres timestamp easily and reliably is an awfully useful
thing.  And if we have EXTRACT(EPOCH ...) why shouldn't we have the
reverse conversion?
        regards, tom lane