Thread: Small documentation patch
Kind people, This patch shows how to change UNIX timestamps into PostgreSQL timestamps, and clarifies how PERFORM works in PL/PgSQL. :) Cheers, D -- David Fetter david@fetter.org http://fetter.org/ phone: +1 510 893 6100 cell: +1 415 235 3778
Attachment
> + 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