Seconds since UNIX epoch in Oracle

Created: 01 Aug 2007

I have recently been asked how, using Oracle it would be possible to calculate the number of seconds since the UNIX epoch at midnight today in the current timezone, which is British time, currently BST (+01:00).

We have a commercial application which stores timestamps in “UNIX time”: the number of seconds since the UNIX epoch (00:00 on 01/01/1970). We need to manipulate this data to stop this commercial software making naive decisions which affect our revenue. So, within Oracle how do we find out how many seconds there were since the UNIX epoch at midnight today?

Unfortunately Oracle DATETIME datatypes have no concept of timezone or daylight saving time, so asking something like this:

 SELECT to_date('01-AUG-2007','DD-MON-YYYY') - to_date('01-JAN-1970','DD-MON-YYYY') "days since epoch"
FROM   dual;

days since epoch
----------------
           13726

gives you the right answer in one sense, in that there were 13726 days in that period, but the wrong answer if you want to know how many 24-hour periods there were. 1st August 2007 00:00 BST is actually 31st July 2007 23:00 GMT, so really there were 13725.958 24 hour periods in that time.

Happily Oracle do provide the TIMESTAMP WITH TIMEZONE datatype which knows all about time zones and daylight saving. Firstly you need to tell Oracle which time zone you are in:

 >alter session set time_zone='Europe/London';

Session altered.

Then you’ll find that time deltas across daylight saving changes will start working as you want them to:

 SELECT current_timestamp - to_timestamp_tz('01-JAN-1970 00:00','DD-MON-YYYY TZH:TZM') delta,
       to_char(current_timestamp,'HH24:MI:SS TZH:TZM') now
FROM   dual;

DELTA                          NOW
------------------------------ -----------------------------
+000013726 19:58:33.200125000  20:58:33 +01:00

Putting it all together with the use of the extract function, we have this:

 set serveroutput on

ALTER SESSION SET TIME_ZONE='Europe/London';

DECLARE
        days INTEGER;
        hours INTEGER;
        unixmidnight INTEGER;
BEGIN

        days := EXTRACT(DAY FROM(TRUNC(CURRENT_TIMESTAMP, 'DD') -
                TO_TIMESTAMP_TZ('01-JAN-1970 00:00','DD-MON-YYYY TZH:TZM')) DAY TO SECOND);
        hours := EXTRACT(HOUR FROM(TRUNC(CURRENT_TIMESTAMP, 'DD') -
                TO_TIMESTAMP_TZ('01-JAN-1970 00:00','DD-MON-YYYY TZH:TZM')) DAY TO SECOND);

        unixmidnight := days * 24 * 60 * 60 + hours * 60 * 60;

        DBMS_OUTPUT.PUT_LINE(TO_CHAR(unixmidnight) 
               || ' seconds since unix epoch at midnight today in London');

END;
/

Session altered.

1185922800 seconds since unix epoch at midnight today in London

PL/SQL procedure successfully completed.

It’s hardly elegant, but it might save someone some work.