To: "Richard Huxton" <dev@archonet.com>
cc: "Roberto Mello" <rmello@cc.usu.edu>, pgsql-sql@postgresql.org
Subject: Re: Converting epoch to timestamp? 
In-reply-to: <001b01c11a63$f86779e0$1001a8c0@archonet.com> 
References: <20010801005708.A16786@cc.usu.edu>
	<001b01c11a63$f86779e0$1001a8c0@archonet.com>
Comments: In-reply-to "Richard Huxton" <dev@archonet.com>
	message dated "Wed, 01 Aug 2001 09:28:39 +0100"
Date: Wed, 01 Aug 2001 10:02:53 -0400
Message-ID: <9309.996674573@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

"Richard Huxton" <dev@archonet.com> writes:
>> I searched the docs for function to convert epoch to timestamps but
>> couldn't find any. Are there any?

> richardh=> select '1970-01-01'::date + '996654342 seconds'::interval;
>         ?column?
> ------------------------
>  2001-08-01 08:25:42+01
> (1 row)

> That's the only way I've ever known to do it. Note the one-hour offset
> because I'm currently in BST rather than GMT timezone (ignore the few
> seconds discrepancy - that's me querying then cutting and pasting).

Yes.  In timezones further away from Greenwich, it'd be a lot worse :-(
-- the problem is that '1970-01-01'::date will be interpreted as
midnight local time.  A more reliable way is to specify the epoch as
a full timestamp, rather than letting the system promote date to
timestamp:

regression=# select now(), date_part('epoch', now());
          now           | date_part
------------------------+-----------
 2001-08-01 09:52:34-04 | 996673954
(1 row)

regression=# select '1970-01-01 00:00 GMT'::timestamp  + '996673954 sec'::interval;
        ?column?
------------------------
 2001-08-01 09:52:34-04
(1 row)

> Be interested to find out if there's a neater way. Can't believe there's not
> some EPOCH_BASE constant that could be used.

Now that you mention it, there is:

regression=# select 'epoch'::timestamp  + '996673954 sec'::interval;
        ?column?
------------------------
 2001-08-01 09:52:34-04
(1 row)


Personally, though, I tend to rely on the binary equivalence between
abstime and Unix-timestamp integers:

regression=# select 996673954::int4::abstime::timestamp;
        ?column?
------------------------
 2001-08-01 09:52:34-04
(1 row)

which can be written more presentably as

regression=# select timestamp(996673954);
       timestamp
------------------------
 2001-08-01 09:52:34-04
(1 row)

(This last didn't use to work, but it seems fine in 7.0 and 7.1.  It
will fail in 2038 when timestamps stop looking like int4, but by then
hopefully we'll have changed things...)

			regards, tom lane