Convert Oracle's timestamp to seconds from epoch

Question

I have a timestamp stored on a table in an Oracle Database, such as:

01/03/12 16:13:33,000000000

I want to convert it to seconds since Unix's Epoch to return in a query. What would be the easiestway to do it?

EDIT: oh, I need timestamp precision and can't rely on Date type here, as it has less precision.

An Oracle DATE has precision to the second. An Oracle TIMESTAMP has subsecond precision. But a Unix epoch generally only has precision to the second. So I'm not sure I understand your comment about precision. If your timestamp has milliseconds or nanoseconds, for example, how would you want to translate that into a Unix epoch? do you want to return a non-integer number? — Justin Cave

Bob Jarvis - Reinstate Monica Bob Jarvis - Reinstate Monica · Accepted Answer · 2012-05-11T17:38:19

Perhaps

WITH I AS (SELECT (TO_TIMESTAMP_TZ('01/03/12 16:13:33,000000000' || SESSIONTIMEZONE,
                                   'DD/MM/RR HH24:MI:SS,FF9TZH:TZM') - 
                   TO_TIMESTAMP_TZ('01/01/1970 00:00:00 GMT',
                                   'DD/MM/YYYY HH24:MI:SS TZR')) AS UNIX_INTERVAL
             FROM DUAL)
SELECT (EXTRACT(DAY FROM UNIX_INTERVAL)  * 86400) +
       (EXTRACT(HOUR FROM UNIX_INTERVAL) * 3600) +
       (EXTRACT(MINUTE FROM UNIX_INTERVAL) * 60) + 
       (EXTRACT(SECOND FROM UNIX_INTERVAL))
  FROM I

will get you started. This assumes a 01-Jan-1970 00:00:00Z epoch date.

Share and enjoy.

Convert Oracle's timestamp to seconds from epoch

1 Answers