Re: working with unix timestamp

Gary Stainburn <gary(dot)stainburn(at)ringways(dot)co(dot)uk> writes:
> How can I insert the integer timestamp in $timestamp into my table?

The "clean" way is

select 'epoch'::timestamptz + <integer> * '1 second'::interval;

for instance

regression=# select 'epoch'::timestamptz + 1079459165 * '1 second'::interval;
?column?
------------------------
2004-03-16 12:46:05-05
(1 row)

The "dirty" way is to rely on abstime being binary-compatible with int4:

regression=# select 1079459165::abstime::timestamptz;
timestamptz
------------------------
2004-03-16 12:46:05-05
(1 row)

This is probably a tad faster, but abstime is deprecated and will
disappear sometime before Y2038 becomes an issue. Also, this *only*
works for integers, whereas the other way handles fractional seconds
just fine.

BTW, the reverse transformation is extract(epoch):

regression=# select extract(epoch from '2004-03-16 12:46:05-05'::timestamptz);
date_part
------------
1079459165
(1 row)

Note that I have been careful to work with timestamp with time zone
(timestamptz) here. If you work with timestamp without time zone,
your results will be off by your GMT offset.

regards, tom lane