From: Tom Lane <tgl@sss.pgh.pa.us>
To: Richard Huxton <dev@archonet.com>
Cc: Roberto Mello <rmello@cc.usu.edu>
Cc: pgsql-sql@postgresql.org
Subject: Re: Converting epoch to timestamp?
Date: Wed, 01 Aug 2001 10:02:53 -0400
Message-ID: <9309.996674573@sss.pgh.pa.us> (raw)
In-Reply-To: <001b01c11a63$f86779e0$1001a8c0@archonet.com>
References: <20010801005708.A16786@cc.usu.edu>
<001b01c11a63$f86779e0$1001a8c0@archonet.com>
"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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, dev@archonet.com, rmello@cc.usu.edu
Subject: Re: Converting epoch to timestamp?
In-Reply-To: <9309.996674573@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox