Home
The Toolkit for Online Communities
16683 Community Members, 2 members online, 2291 visitors today
Log In Register
OpenACS Home : Forums : OpenACS Q&A : Epoch from Oracle

Forum OpenACS Q&A: Epoch from Oracle

Icon of envelope Request notifications

Collapse
Posted by Lars Pind on
In PG you can get the date_part('epoch',some_date_col) to get the
date in a way you can format with ns_fmttime.

What's the equivalent in Oracle?

/Lars

Collapse
Posted by Paul Sharples on
How about:
        86400 * (some_date_col - TO_DATE('01.01.1970','DD.MM.YYYY'))


Consider this example from the Postgres documentation:
   SELECT EXTRACT(EPOCH FROM TIMESTAMP '2001-02-16 20:38:40');
   Result: 982352320


Plugging it into the Oracle version:
   SQL> SELECT 86400 * (TO_DATE('16.02.2001:20:38:40','DD.MM.YYYY:HH24:MI:SS')-
                        TO_DATE('01-JAN-1970','DD-MON-YYYY')) x
          FROM dual;

            X
   ----------
    982355920

   SQL>
Which is exactly 3600 seconds out. Hmm. Who want to be the first to point the finger?

Paul

Collapse
Posted by Don Baccus on
You wouldn't happen to be in a timezone 3600 seconds different than GMT, would you?
Collapse
Posted by Paul Sharples on
Possibly. I didn't actually run the Postgresql example (just copied it from the docs); maybe that was run originally on a machine with a GMT offset.

The Oracle query has used absolute values so I can't see how the system clock could affect the result.

Good suggestion, though...

Collapse
Posted by Don Baccus on
Check this out:
acs=# select extract(epoch from timestamp '2001-02-16 20:38:40-00');
 date_part
-----------
 982355920
(1 row)
Your example was being computed in local time wherever it was run ...
Collapse
Posted by Paul Sharples on
Thanks for running that, Don (I don't have a Postgres instance available). I'm speculating wildly here, as my experience with PG is a heavily laden [African] swallow's altitude above nil, but it seems to me that the result printed in the documentation is wrong, or at the very least misleading.

Still, short of more experimentation, I couldn't comment further.

Cheers.

Collapse
Posted by Don Baccus on
The example's not wrong or even surprising if you understand that the timestamp type converts strings without an explicit timezone to local time, not GMT.  The example assumes you've read the manual's explanation of how string to timestamp conversion is performed.
Collapse
Posted by Paul Sharples on
Ooh, hit me with that manual harder, baby!

So if you asked someone in Australia to run the same query on their PG instance you'd expect a different answer?

Whatever. Oracle can be given the same behaviour by simply adjusting the constant in the to_date('01-JAN-1970',blahblahblah).

Collapse
Posted by Paul Sharples on
On reflection, my last comment was flippant. It's just dawning on me that timestamp handling has more ramifications than I've considered up til now. Working for a business and living in a country covered by one timezone allows one to safely ignore most of these issues.

I'm still having problems understanding why the PG docs have a different result listed for their example though. Obviously, I'll never make it as a Timelord :-(

Collapse
Posted by Vinod Kurup on
The example listed in the PG docs is different than the example that Don posted. Don's version has '-00' at the end of the time string to denote GMT time whereas the PG docs version is using local time. This explains the discrepancy in results and therefore why all the examples posted are technically correct.
Collapse
11: Re: Epoch from Oracle (response to 1)
Posted by Thomas Munro on
Isn't the * 86000 approach ignoring daylight savings?
Collapse
12: Re: Epoch from Oracle (response to 1)
Posted by Mark Aufflick on
Someone from Australia here:

pump2=# select extract(epoch from timestamp '2001-02-16 20:38:40-00');
 date_part 
-----------
 982316320
(1 row)
;)