0

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?

MT0
  • 143,790
  • 11
  • 59
  • 117
user1071914
  • 3,295
  • 11
  • 50
  • 76
  • 1
    Usually Unix time is seconds since 1970-01-01 00:00:00 UTC - are you sure about 1906? – Wernfried Domscheit Oct 10 '19 at 17:28
  • Usually UNIX time, in addition to being measured from 1 January 1970, is a timestamp WITH TIME ZONE. It doesn't make much sense to apply it to an oracle DATE value, which does not have time zone information. –  Oct 10 '19 at 17:38

1 Answers1

1

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'.

Littlefoot
  • 131,892
  • 15
  • 35
  • 57