oracle-databasedatetime

oracle query to round time to lower 15 minute window

I have a query which rounds the time to the nearest 15 minutes window:

Select     Sysdate,
           Trunc (Sysdate) + ( Round ( (Sysdate - Trunc (Sysdate))* 96)/ 96)
FROM       dual;

However, I don't want the nearest but the lower 15 minutes window.

e.g. 1:

Time 19:57:03
Above query output 20:00:00
Required output 19:45:00

e.g 2:

Time 17:18:00
Above query output 17:15:00
required output 17:15:00

The second example is correct but the first one rounds to 20:00 as its nearer point. However I want query to output 19:45 for first example.

Solution

  • You can use Floor And Ceil Concept.

    Select     Sysdate,
           Trunc (Sysdate) + ( Round (floor(Sysdate - Trunc (Sysdate))* 96)/ 96)
FROM       dual;

    For reference, Please visit Here