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:

[pastacode lang=”sql” manual=”select%20(extract(day%20from%20(EVENT_TIME%20%20-%20to_date(’01-JAN-1970’%2C’DD-MON-YYYY’)))*86400%2Bextract(hour%20from%20EVENT_TIME)*3600%2Bextract(minute%20from%20EVENT_TIME)*60%2Bextract(second%20from%20EVENT_TIME))%20as%20EPOCH%20from%20SOMETABLE%20order%20by%20event_time%20DESC%3B%0A” message=”” highlight=”” provider=”manual”/]

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.