X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id 8E5EED1E917
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 16 Mar 2004 17:52:33 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 62313-08
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 16 Mar 2004 13:52:31 -0400 (AST)
Received: from sss.pgh.pa.us (unknown [192.204.191.242])
	by svr1.postgresql.org (Postfix) with ESMTP id 43B8BD1E934
	for <pgsql-sql@postgresql.org>; Tue, 16 Mar 2004 13:52:28 -0400 (AST)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.12.11/8.12.11) with ESMTP id i2GHqQ6Y026055;
	Tue, 16 Mar 2004 12:52:27 -0500 (EST)
To: Gary Stainburn <gary.stainburn@ringways.co.uk>
Cc: pgsql-sql@postgresql.org
Subject: Re: working with unix timestamp 
In-reply-to: <200403161654.18049.gary.stainburn@ringways.co.uk> 
References: <200403161654.18049.gary.stainburn@ringways.co.uk>
Comments: In-reply-to Gary Stainburn <gary.stainburn@ringways.co.uk>
	message dated "Tue, 16 Mar 2004 16:54:18 +0000"
Date: Tue, 16 Mar 2004 12:52:26 -0500
Message-ID: <26054.1079459546@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200403/180
X-Sequence-Number: 17279

Gary Stainburn <gary.stainburn@ringways.co.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