tonyf tonyf - 3 months ago 9
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.

Thanks.

Answer

You probably want something like

startTime - interval '30' minute >= sysdate

or

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.