Forum OpenACS Q&A: Epoch from Oracle
date in a way you can format with ns_fmttime.
What's the equivalent in Oracle?
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?
The Oracle query has used absolute values so I can't see how the system clock could affect the result.
Good suggestion, though...
Your example was being computed in local time wherever it was run ...acs=# select extract(epoch from timestamp '2001-02-16 20:38:40-00'); date_part ----------- 982355920 (1 row)
Still, short of more experimentation, I couldn't comment further.
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).
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