Oracle Date to Unix Timestamp

by @jehiah on 2006-08-23 18:11UTC
Filed under: All , Articles , Programming , mysql , SQL , Oracle

Here’s a problem : how do you get SYSDATE back as an integer number of seconds from epoc in Oracle.

It turns out it’s quite hard …. even though thats how it’s stored internally, and number of seconds from epoc is a fairly standard date format.

So here is what I was able to find. It seems way to wordy and I hope one of my readers can come up with an easier solution.

[sql]
SELECT (sysdate - to_date('01-JAN-1970','DD-MON-YYYY')) * (86400) as dt FROM dual;  
[/sql]

The value returned is as follows

[code]
1156307386
[/code]

You can get the same timestamp format from the linux command date +%s, from the mysql function unix_timestamp or by calling time.time() in python

Credit goes to google for eventually finding this page with the answer

Subscribe via RSS ı Email
Jehiah Czebotar