Our Blog. We have some things we'd like to share.

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)
Tagged:

Leave a Response


Cincinnati 513.298.1865

Virginia 7875 Promontory Court Dunn Loring, VA 22027

Kentucky 12910 Shelbyville Road Suite 310 Louisville, KY 40243 502.245.6756

© 2010 Mission Data twitter