Small documentation patch

+ SELECT 'epoch'::timestamp + '1070430858 seconds'::interval;
+ <lineannotation>Result: </lineannotation><computeroutput>2003-12-03 05:54:18</computeroutput>
</screen>

You could also go:

SELECT '1070430858'::abstime;

But your way is probably a bit more stable...dunno...

Chris

On Wed, 3 Dec 2003, Christopher Kings-Lynne wrote:

+ SELECT 'epoch'::timestamp + '1070430858 seconds'::interval;
+ <lineannotation>Result: </lineannotation><computeroutput>2003-12-03 05:54:18</computeroutput>
</screen>

You could also go:

SELECT '1070430858'::abstime;

But your way is probably a bit more stable...dunno...

template1=# SELECT '1070430858'::abstime;
ERROR: invalid input syntax for type abstime: "1070430858"

I agree its more stable :-). That's on HEAD.

Gavin

On Wed, Dec 03, 2003 at 05:59:25PM +1100, Gavin Sherry wrote:

On Wed, 3 Dec 2003, Christopher Kings-Lynne wrote:

+ SELECT 'epoch'::timestamp + '1070430858 seconds'::interval;
+ <lineannotation>Result: </lineannotation><computeroutput>2003-12-03 05:54:18</computeroutput>
</screen>

You could also go:

SELECT '1070430858'::abstime;

But your way is probably a bit more stable...dunno...

template1=# SELECT '1070430858'::abstime;
ERROR: invalid input syntax for type abstime: "1070430858"

I agree its more stable :-). That's on HEAD.

Gavin

I like "more stable." "Clearer" is good, too :) :)

Cheers,
D
--
David Fetter david@fetter.org http://fetter.org/
phone: +1 510 893 6100 cell: +1 415 235 3778

template1=# SELECT '1070430858'::abstime;
ERROR: invalid input syntax for type abstime: "1070430858"

I agree its more stable :-). That's on HEAD.

Whoops:

SELECT 1070430858::abstime::timestamp;

Chris

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

On Wed, Dec 03, 2003 at 10:49:01AM -0500, Tom Lane wrote:

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.

Great! I am not attached to any particular way of doing this, just as
long as some clue about converting UNIX timestamps into PostgreSQL
timestamps gets in there :)

Cheers,
D
--
David Fetter david@fetter.org http://fetter.org/
phone: +1 510 893 6100 cell: +1 415 235 3778