Received: from sss.pgh.pa.us ([192.204.191.242]) by postgresql.org (8.11.3/8.11.4) with ESMTP id g0A1xDB86799 for ; Wed, 9 Jan 2002 20:59:13 -0500 (EST) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.4/8.11.4) with ESMTP id g0A1x5g17262; Wed, 9 Jan 2002 20:59:05 -0500 (EST) To: "Christopher Kings-Lynne" cc: pgsql-sql@postgresql.org Subject: Re: converting an epoch to a timestamp In-reply-to: References: Comments: In-reply-to "Christopher Kings-Lynne" message dated "Thu, 10 Jan 2002 09:25:27 +0800" Date: Wed, 09 Jan 2002 20:59:04 -0500 Message-ID: <17259.1010627944@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200201/72 X-Sequence-Number: 6162 "Christopher Kings-Lynne" writes: > How does one go about converting a UNIX epoch (ie. int4) to a timestamp? I tend to rely on the binary equivalence between int4 and abstime: regression=# select 1010626671::int4::abstime::timestamp; timestamptz ------------------------ 2002-01-09 20:37:51-05 (1 row) which can be written in a less ugly-looking though equivalent fashion: regression=# select "timestamptz"(1010626671); timestamp --------------------- 2002-01-09 20:37:51-05 (1 row) (In 7.1.3 you'd just write "timestamp" without the "tz". Note that the quotes are not optional in 7.2.) However, Thomas keeps threatening to remove type abstime. There are more socially acceptable ways of doing it, eg regression=# select 'epoch'::timestamptz + '1010626671 sec'::interval; ?column? ------------------------ 2002-01-09 20:37:51-05 (1 row) but this way does not work well when the integer in question is already in the database, rather than being plugged into a literal query string by an application. I think some people have recommended regression=# select 'epoch'::timestamptz + "interval"(1010626671::int4); ?column? ------------------------ 2002-01-17 20:37:51-05 (1 row) but as far as I can see that's just an extra runtime operation without any redeeming social value, because when you look under the hood at the interval coercion, you find out that it's relying on binary equivalence between int4 and reltime ... so this too will break if we get rid of the obsolete time types. Personally I'd like to see an officially supported int4-to-timestamp (or perhaps better double-to-timestamp, to be Y2038-compliant and fractional-second-capable) conversion function. Getting from Unix timestamp to Postgres timestamp easily and reliably is an awfully useful thing. And if we have EXTRACT(EPOCH ...) why shouldn't we have the reverse conversion? regards, tom lane