Convert "epoch" to timestamp

On Tue, 23 Apr 2002, Jeff Eckermann wrote:

jeff=# select version();
version

-------------------------------------------------------------------
PostgreSQL 7.2 on i386-portbld-freebsd4.5, compiled
by GCC 2.95.3
(1 row)

I need to convert the "epoch" (i.e. number of seconds)
timestamp to a PostgreSQL timestamp.

The extract/date_part functions do it easily the other
way, but I have had some difficulty in doing what I
need.

According to posts in the archives, the following used
to work:

jeff=# select extract (epoch from
current_timestamp(0));
date_part
------------
1019571752
(1 row)

jeff=# select timestamp(1019571752);
ERROR: TIMESTAMP(1019571752) WITH TIME ZONE precision
must be between 0 and 13

You'd need to double quote that timestamp to differentiate
the function from the data type: "timestamp"(1019571752)

...

Can I rely on this continuing to work? Apparently
not, because the docs say (wrt "abstime" and
"reltime"): "Any or all of these internal types might
disappear in a future release."

Right. But it won't disappear from the version you are running ;)

The following also works:

lockhart=# select timestamp without time zone 'today',
lockhart=> timestamp without time zone '1970-01-01'
lockhart=> + cast('1019520000' as interval);
timestamp | ?column?
---------------------+---------------------
2002-04-23 00:00:00 | 2002-04-23 00:00:00

I'm not sure what other features for integer->timestamp conversions may
be available in the future, but the above conforms to SQL9x standards so
is likely to be a good choice...

- Thomas

Jeff Eckermann <jeff_eckermann@yahoo.com> writes:

According to posts in the archives, the following used
to work:

jeff=# select timestamp(1019571752);
ERROR: TIMESTAMP(1019571752) WITH TIME ZONE precision
must be between 0 and 13
jeff=#

Still works if you quote it:

regression=# select "timestamp"(1019571752);
timestamp
---------------------
2002-04-23 10:22:32
(1 row)

regression=# select "timestamptz"(1019571752);
timestamptz
------------------------
2002-04-23 10:22:32-04
(1 row)

However, the cast to abstime may be preferable, since Thomas is unlikely
to keep fiddling with the syntax and semantics of that type ;-) ;-).

Eventually there should be an exact converse of the extract(epoch)
functionality, rather than these various kluges relying on
abstime-to-integer equivalence.

We'll probably start thinking about removing abstime when Unix systems
start to migrate away from 32-bit time_t, which one hopes will happen
well before the year 2038. So eventually something's got to be done.

regards, tom lane