Search code examples
sqloracle-databaseunix-timestamp

Convert Oracle DATE to Unix-style time (seconds since 1906?)

I need to convert an Oracle DATE value to a Unix style seconds-since-epoch-start value.

I've tried various combinations of Oracle's conversions such as:

select to_number(to_date('10/05/2019','mm/dd/yyyy')) from dual;
select to_number(to_timestamp(to_date('10/05/2019','mm/dd/yyyy')))  from dual;
select to_number(to_char(to_date('10/05/2019','mm/dd/yyyy'))) from dual;

Nothing seems to work. Does anyone have an answer to this?

Solution

  • If that's number of seconds since Jan 01 1906, then:

    SQL> select sysdate - date '1906-01-01' days,
  2        (sysdate - date '1906-01-01') * 24 * 60 * 60 unix_style
  3  from dual;

      DAYS UNIX_STYLE
---------- ----------
 41555,811 3590422068

SQL>

    Why? Because - when you subtract two dates in Oracle, result is number of days. Then you have to multiply it by 24 (as there are 24 hours in a day), by 60 (as there are 60 minutes in an hour) and once again by 60 (as there are 60 seconds in a minute).

    Of course, you could have multiplied it by 86400 (which is 24 * 60 * 60), but - former is difficult to understand while latter shows what's going on and why.

    If - as Wernfried commented - date differs from the one you said, you'd just replace date '1906-01-01' with date '1970-01-01'.