Postgres Pro
Downloads
Home   >   mailing lists

Thread: Converting epoch to timestamp?

Converting epoch to timestamp?

From
Roberto Mello
Date:
I searched the docs for function to convert epoch to timestamps but
couldn't find any. Are there any?
Thanks,
-Roberto
-- 
+------------| Roberto Mello - http://www.brasileiro.net |------------+    Computer Science, Utah State University -
http://www.usu.edu     USU Free Software & GNU/Linux Club - http://fslc.usu.edu        Space Dynamics Lab, Developer
http://www.sdl.usu.edu
[<<] [>] [>>] [o] [||] [|>]

Re: Converting epoch to timestamp?

From
"Richard Huxton"
Date:
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

Re: Converting epoch to timestamp?

From
Roberto Mello
Date:
On Wed, Aug 01, 2001 at 09:28:39AM +0100, Richard Huxton wrote:

> Hi Roberto - long time no see.
Hey Richard. Yeah. Summer classes and summer jobs :-) I have to finish
my expanded "Porting From Oracle" thingy.

>
> richardh=> select '1970-01-01'::date + '996654342 seconds'::interval;
>         ?column?
> ------------------------
>  2001-08-01 08:25:42+01
> (1 row)
Duh! Guess I could have thought of that. Thanks a lot.
-Roberto
-- 
+------------| Roberto Mello - http://www.brasileiro.net |------------+    Computer Science, Utah State University -
http://www.usu.edu     USU Free Software & GNU/Linux Club - http://fslc.usu.edu        Space Dynamics Lab, Developer
http://www.sdl.usu.edu
Veni, Vidi, VCR - I came, I saw, I videotaped it

Re: Converting epoch to timestamp?

From
Tom Lane
Date:
"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

Re: Converting epoch to timestamp?

From
"Josh Berkus"
Date:
Tom,

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

Yeah, sure.  That's what my father said in 1964 when they talked about
the potential problems with 2-digit dates on the UNIVAC II ...

;-)

-Josh


______AGLIO DATABASE SOLUTIONS___________________________                                      Josh Berkus Complete
informationtechnology      josh@agliodbs.com  and data management solutions       (415) 565-7293 for law firms, small
businesses       fax 621-2533   and non-profit organizations.      San Francisco