Forum OpenACS Q&A: Epoch from Oracle

Collapse
1: Epoch from Oracle
Posted by Lars Pind on 08/22/02 10:46 AM
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
2: Response to Epoch from Oracle (response to 1)
Posted by Paul Sharples on 08/22/02 02:28 PM
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
3: Response to Epoch from Oracle (response to 1)
Posted by Don Baccus on 08/22/02 03:46 PM
You wouldn't happen to be in a timezone 3600 seconds different than GMT, would you?
Collapse
4: Response to Epoch from Oracle (response to 1)
Posted by Paul Sharples on 08/22/02 04:43 PM
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
5: Response to Epoch from Oracle (response to 1)
Posted by Don Baccus on 08/22/02 08:44 PM
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
6: Response to Epoch from Oracle (response to 1)
Posted by Paul Sharples on 08/23/02 12:47 AM
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
7: Response to Epoch from Oracle (response to 1)
Posted by Don Baccus on 08/23/02 12:58 AM
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
8: Response to Epoch from Oracle (response to 1)
Posted by Paul Sharples on 08/23/02 10:07 AM
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
9: Response to Epoch from Oracle (response to 1)
Posted by Paul Sharples on 08/23/02 10:32 AM
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
10: Response to Epoch from Oracle (response to 1)
Posted by Vinod Kurup on 08/27/02 06:00 AM
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 06/16/04 03:42 AM
Isn't the * 86000 approach ignoring daylight savings?
Collapse
12: Re: Epoch from Oracle (response to 1)
Posted by Mark Aufflick on 06/16/04 05:24 PM
Someone from Australia here:

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