Message-ID: <001b01c11a63$f86779e0$1001a8c0@archonet.com>
From: "Richard Huxton" <dev@archonet.com>
To: "Roberto Mello" <rmello@cc.usu.edu>, <pgsql-sql@postgresql.org>
References: <20010801005708.A16786@cc.usu.edu>
Subject: Re: Converting epoch to timestamp?
Date: Wed, 1 Aug 2001 09:28:39 +0100
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit

From: "Roberto Mello" <rmello@cc.usu.edu>

Hi Roberto - long time no see.

> I searched the docs for function to convert epoch to timestamps but
> couldn't find any. Are there any?

richardh=> select now();
          now
------------------------
 2001-08-01 09:25:58+01
(1 row)

richardh=> select extract('epoch' from now());
 date_part
-----------
 996654359
(1 row)

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).

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.

- Richard Huxton