pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedConverting epoch to timestamp?
9+ messages / 6 participants
[nested] [flat]
* Converting epoch to timestamp?
@ 2001-08-01 06:57 Roberto Mello <rmello@cc.usu.edu>
2001-08-01 08:28 ` Re: Converting epoch to timestamp? Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 9+ messages in thread
From: Roberto Mello @ 2001-08-01 06:57 UTC (permalink / raw)
To: pgsql-sql
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] [||] [|>]
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Converting epoch to timestamp?
2001-08-01 06:57 Converting epoch to timestamp? Roberto Mello <rmello@cc.usu.edu>
@ 2001-08-01 08:28 ` Richard Huxton <dev@archonet.com>
2001-08-01 13:29 ` Re: Converting epoch to timestamp? Roberto Mello <rmello@cc.usu.edu>
2001-08-01 14:02 ` Re: Converting epoch to timestamp? Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 9+ messages in thread
From: Richard Huxton @ 2001-08-01 08:28 UTC (permalink / raw)
To: Roberto Mello <rmello@cc.usu.edu>; pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Converting epoch to timestamp?
2001-08-01 06:57 Converting epoch to timestamp? Roberto Mello <rmello@cc.usu.edu>
2001-08-01 08:28 ` Re: Converting epoch to timestamp? Richard Huxton <dev@archonet.com>
@ 2001-08-01 13:29 ` Roberto Mello <rmello@cc.usu.edu>
1 sibling, 0 replies; 9+ messages in thread
From: Roberto Mello @ 2001-08-01 13:29 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Converting epoch to timestamp?
2001-08-01 06:57 Converting epoch to timestamp? Roberto Mello <rmello@cc.usu.edu>
2001-08-01 08:28 ` Re: Converting epoch to timestamp? Richard Huxton <dev@archonet.com>
@ 2001-08-01 14:02 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 9+ messages in thread
From: Tom Lane @ 2001-08-01 14:02 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: Roberto Mello <rmello@cc.usu.edu>; pgsql-sql
"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
^ permalink raw reply [nested|flat] 9+ messages in thread
* converting epoch to timestamp
@ 2005-10-26 09:05 Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
2005-10-26 09:22 ` Re: converting epoch to timestamp Richard Huxton <dev@archonet.com>
2005-10-26 09:25 ` Re: converting epoch to timestamp A. Kretschmer <andreas.kretschmer@schollglas.com>
2005-10-26 09:31 ` Re: converting epoch to timestamp Stef <svb@ucs.co.za>
0 siblings, 3 replies; 9+ messages in thread
From: Rajesh Kumar Mallah @ 2005-10-26 09:05 UTC (permalink / raw)
To: pgsql-sql
Hi,
Can anyone tell me how to convert epoch to timestamp ?
ie reverse of :
SELECT EXTRACT( epoch FROM now() );
+------------------+
| date_part |
+------------------+
| 1130317518.61997 |
+------------------+
(1 row)
Regds
mallah.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: converting epoch to timestamp
2005-10-26 09:05 converting epoch to timestamp Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
@ 2005-10-26 09:22 ` Richard Huxton <dev@archonet.com>
2005-10-28 04:28 ` Re: converting epoch to timestamp Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
2 siblings, 1 reply; 9+ messages in thread
From: Richard Huxton @ 2005-10-26 09:22 UTC (permalink / raw)
To: Rajesh Kumar Mallah <mallah.rajesh@gmail.com>; +Cc: pgsql-sql
Rajesh Kumar Mallah wrote:
> Hi,
>
> Can anyone tell me how to convert epoch to timestamp ?
>
> ie reverse of :
>
> SELECT EXTRACT( epoch FROM now() );
I'd start with either Google or the manuals.
http://www.postgresql.org/docs/8.0/interactive/functions-datetime.html
Scroll down to the section on "epoch" here and see the example.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: converting epoch to timestamp
2005-10-26 09:05 converting epoch to timestamp Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
2005-10-26 09:22 ` Re: converting epoch to timestamp Richard Huxton <dev@archonet.com>
@ 2005-10-28 04:28 ` Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
0 siblings, 0 replies; 9+ messages in thread
From: Rajesh Kumar Mallah @ 2005-10-28 04:28 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: pgsql-sql
On 10/26/05, Richard Huxton <dev@archonet.com> wrote:
> Rajesh Kumar Mallah wrote:
> > Hi,
> >
> > Can anyone tell me how to convert epoch to timestamp ?
> >
> > ie reverse of :
> >
> > SELECT EXTRACT( epoch FROM now() );
>
> I'd start with either Google or the manuals.
>
> http://www.postgresql.org/docs/8.0/interactive/functions-datetime.html
Firstly Thanks everyone for the response.
I did read this document but not too carefully hence missed.
I missed because i was not careful and partly because i was not expecting that
little note to be under documentation of EXTRACT which deals with getting
date/time sub fields. I am no documentation expert just trying to explain
why i could not find it.
PS: sorry for late reply
Regds
Mallah.
>
> Scroll down to the section on "epoch" here and see the example.
>
> --
> Richard Huxton
> Archonet Ltd
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: converting epoch to timestamp
2005-10-26 09:05 converting epoch to timestamp Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
@ 2005-10-26 09:25 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2 siblings, 0 replies; 9+ messages in thread
From: A. Kretschmer @ 2005-10-26 09:25 UTC (permalink / raw)
To: pgsql-sql
am 26.10.2005, um 14:35:51 +0530 mailte Rajesh Kumar Mallah folgendes:
> Hi,
>
> Can anyone tell me how to convert epoch to timestamp ?
>
> ie reverse of :
>
> SELECT EXTRACT( epoch FROM now() );
> +------------------+
> | date_part |
> +------------------+
> | 1130317518.61997 |
> +------------------+
> (1 row)
SELECT TIMESTAMP WITH TIME ZONE 'epoch' + 1130317518.61997 * INTERVAL '1 second';
HTH, Andreas
--
Andreas Kretschmer (Kontakt: siehe Header)
Heynitz: 035242/47212, D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
=== Schollglas Unternehmensgruppe ===
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: converting epoch to timestamp
2005-10-26 09:05 converting epoch to timestamp Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
@ 2005-10-26 09:31 ` Stef <svb@ucs.co.za>
2 siblings, 0 replies; 9+ messages in thread
From: Stef @ 2005-10-26 09:31 UTC (permalink / raw)
To: Rajesh Kumar Mallah <mallah.rajesh@gmail.com>; +Cc: pgsql-sql
Rajesh Kumar Mallah mentioned :
=> Can anyone tell me how to convert epoch to timestamp ?
=>
=> ie reverse of :
=>
=> SELECT EXTRACT( epoch FROM now() );
=> +------------------+
=> | date_part |
=> +------------------+
=> | 1130317518.61997 |
=> +------------------+
Here is one way (In my case I still had to add/subtract timezone diff)
select '19700101'::timestamp + foo.epoch::interval from (select extract(epoch from now())||' seconds' as epoch) foo ;
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2005-10-28 04:28 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-08-01 06:57 Converting epoch to timestamp? Roberto Mello <rmello@cc.usu.edu>
2001-08-01 08:28 ` Richard Huxton <dev@archonet.com>
2001-08-01 13:29 ` Roberto Mello <rmello@cc.usu.edu>
2001-08-01 14:02 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-10-26 09:05 converting epoch to timestamp Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
2005-10-26 09:22 ` Re: converting epoch to timestamp Richard Huxton <dev@archonet.com>
2005-10-28 04:28 ` Re: converting epoch to timestamp Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
2005-10-26 09:25 ` Re: converting epoch to timestamp A. Kretschmer <andreas.kretschmer@schollglas.com>
2005-10-26 09:31 ` Re: converting epoch to timestamp Stef <svb@ucs.co.za>
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