Computing time with Oracle sysdate
If you ever need to work with something other than a day when using sysdate you are in luck. As it turns out you can work on sysdate with fractional days. This is just what a person needs when they want to do something like:
SELECT mycol FROM sometable WHERE sometimestamp < "30 seconds ago"
The main trick here is to know that sysdate math is based in days and fractional days work. Here are a few examples:
Remove hours from a date:
sysdate - hours/hours in a day
Remove minutes from a date:
sysdate - minutes/(hours in a day * minutes in an hour)
or
sysdate - minutes/minutes in a day
Remove seconds from a date:
sysdate - seconds/(hours in a day * minutes in an hour * seconds in a minute)
or
sysdate - seconds/seconds in a day
The example above would be:
SELECT mycol FROM sometable WHERE sometimestamp < sysdate - 30/(24*60*60)
