tonyf tonyf - 1 year ago 101
SQL Question

How to Convert a Single Oracle Date Time into Minutes

Need help on how to convert a single oracle datetime value to minutes.

As an example, I need to convert the current date/time alone into minutes, i.e.:

select (sysdate)*24*60 from dual

but this is giving me an error.

I basically need to perform a check to see that a certain operation cannot be performed until 30 minutes before a particular date/start time, that exists in the database.

So if the Start Time in the DB is:

24/04/2014 22:00:00 and the current date/time (SYSDATE) is 24/04/2014 21:29:59

then operation CANNOT be performed but if the current date/time (SYSDATE) is:

24/04/2014 21:30:00

then operation CAN be performed.

Hope this makes sense.

Any help on the above, would be great.


Answer Source

You probably want something like

startTime - interval '30' minute >= sysdate


startTime >= sysdate + interval '30' minute

You could also subtract the two date values which gives you the number of days between them and multiply

(startTime - sysdate)*24*60 >= 30

but I generally find the interval notation clearer and easier to read. It's also easier to structure in a way that allows you to use indexes on columns like startTime.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download