I am creating a Oracle fast formula where I am trying to round the time entered by employee to the nearest 1/4 hour.
15-22 minutes = 0.25 hours
23- 37 minutes = 0.50 hours
38-52 minutes = 0.75 hours
53 to 1 hr 7 mins = 1 hour
I am able to get the hours entered.
For Eg - Time entered - 5 PM - 8.25 PM will be 3.42 hours entered Now this should be converted to the nearest 1/4 hour - 3.75.
what calculation formula should I use to convert this to 3.42 hours ?
You can TRUNC
ate the time to the start of the hour and then round the minutes and seconds part to the nearest 15 minutes and add:
SELECT time,
TRUNC(time, 'HH') + ROUND((time - TRUNC(time, 'HH'))*4*24)/4/24 AS rounded_time
FROM times
Which, for the sample data:
CREATE TABLE times (time) AS
SELECT TRUNC(SYSDATE, 'HH') + NUMTODSINTERVAL(LEVEL, 'MINUTE')
FROM DUAL
CONNECT BY LEVEL <= 60;
Outputs:
TIME | ROUNDED_TIME |
---|---|
2023-03-08 08:01:00 | 2023-03-08 08:00:00 |
... | ... |
2023-03-08 08:07:00 | 2023-03-08 08:00:00 |
2023-03-08 08:08:00 | 2023-03-08 08:15:00 |
... | ... |
2023-03-08 08:22:00 | 2023-03-08 08:15:00 |
2023-03-08 08:23:00 | 2023-03-08 08:30:00 |
... | ... |
2023-03-08 08:37:00 | 2023-03-08 08:30:00 |
2023-03-08 08:38:00 | 2023-03-08 08:45:00 |
... | ... |
2023-03-08 08:52:00 | 2023-03-08 08:45:00 |
2023-03-08 08:53:00 | 2023-03-08 09:00:00 |
... | ... |
2023-03-08 09:00:00 | 2023-03-08 09:00:00 |