It seems Oracle DB doesn’t provide a function to create a unix timestamp from an internal datetime. I have to admit – I’m kinda disappointed about that, but OK – its Oracle …

So, how can we get a timestamp from Oracle. I have googled quite a time, but non of the solutions google offered me worked, so i it’s time to think about it by myself and ended up with the following solution:

select (extract(day from (EVENT_TIME  - to_date('01-JAN-1970','DD-MON-YYYY')))*86400+extract(hour from EVENT_TIME)*3600+extract(minute from EVENT_TIME)*60+extract(second from EVENT_TIME)) as EPOCH from SOMETABLE order by event_time DESC;

At first I subtract the start of the epoch from my current timestamp. this will provide me the days since 1970-01-01. Afterwards I extract hours, minutes and seconds from the timestamp and with all those data it’s possible to calc the timestamp of the specific datetime.