Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
> Whoops:
> SELECT 1070430858::abstime::timestamp;
Or you can do
SELECT '1070430858'::int4::abstime::timestamp;
which helps expose the fact that you're really depending on the
int4-to-abstime binary equivalence. This will certainly break in
2038...
The originally proposed documentation patch is flat wrong:
SELECT 'epoch'::timestamp + '1070430858 seconds'::interval;
because it will produce a timestamp without time zone, thus effectively
making the epoch be 1970-1-1 midnight local time. But of course the
correct Unix epoch is 1970-1-1 midnight GMT. So correct code is
SELECT 'epoch'::timestamptz + '1070430858 seconds'::interval;
or you could use
SELECT 'epoch'::timestamptz + 1070430858 * '1 second'::interval;
which has the advantage that it works without weird concatenation
pushups when the numeric value is coming from a variable.
regards, tom lane