tonyf - 4 months ago 13

SQL Question

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`

.