Formula to use to convert the hours entered to 1/4th hour

Question

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 ?

What kind of entry style do you use : a string `5 PM - 8.25 PM` into a character type column or into two independent date (or timestamp) type columns such as col1 : `2023-03-08 17:00:00` and col2 : `2023-03-08 20:25:00` ? — Barbaros Özhan, Mar 08 '23 at 07:14
Difference between 5PM and 8:25PM is 3 hours 25 minutes. As a decimal number, that's 3.42. If you want to round it to "nearest" 1/4 hour, isn't that 3.50 (and not 3.75)? — Littlefoot, Mar 08 '23 at 07:19

score 2 · Answer 1 · answered Mar 08 '23 at 08:23

With a bit of arithmetic using the Oracle DATE differenz. Calulation 1 is one day, 1 / (24 * 4) is a quartel of an hour, you may calculate

with dt as (
select date'2023-01-01' +  rownum/(24*60) date_dt from dual 
connect by level <= 24*60 - 1)
select date_dt,
round((date_dt - trunc(date_dt)) * 24 * 4) closest_qh,
trunc(date_dt) + round((date_dt - trunc(date_dt)) * 24 * 4) / (24 * 4) rounded_date,
round((date_dt - trunc(date_dt)) * 24 * 4) / 4 formula
from dt;

output (cut for brevity)

DATE_DT             CLOSEST_QH ROUNDED_DATE           FORMULA
------------------- ---------- ------------------- ----------
...
01.01.2023 03:21:00         13 01.01.2023 03:15:00       3,25
01.01.2023 03:22:00         13 01.01.2023 03:15:00       3,25
01.01.2023 03:23:00         14 01.01.2023 03:30:00        3,5
01.01.2023 03:24:00         14 01.01.2023 03:30:00        3,5
...
01.01.2023 03:36:00         14 01.01.2023 03:30:00        3,5
01.01.2023 03:37:00         14 01.01.2023 03:30:00        3,5
01.01.2023 03:38:00         15 01.01.2023 03:45:00       3,75
01.01.2023 03:39:00         15 01.01.2023 03:45:00       3,75
...
01.01.2023 03:51:00         15 01.01.2023 03:45:00       3,75
01.01.2023 03:52:00         15 01.01.2023 03:45:00       3,75
01.01.2023 03:53:00         16 01.01.2023 04:00:00          4
01.01.2023 03:54:00         16 01.01.2023 04:00:00          4

MT0 · Accepted Answer · 2023-03-08T08:57:31.997

You can TRUNCate 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

fiddle

score 0 · Answer 3 · answered Mar 08 '23 at 07:35

This is how I understood it (with some sample data and step-by-step calculation; you'd use result only).

SQL> with test (id, date_from, date_to) as
  2    (select 1, to_date('08.03.2023 17:00', 'dd.mm.yyyy hh24:mi'), to_date('08.03.2023 20:25', 'dd.mm.yyyy hh24:mi') from dual union all
  3     select 2, to_date('08.03.2023 17:00', 'dd.mm.yyyy hh24:mi'), to_date('08.03.2023 20:42', 'dd.mm.yyyy hh24:mi') from dual union all
  4     select 3, to_date('08.03.2023 17:00', 'dd.mm.yyyy hh24:mi'), to_date('08.03.2023 20:06', 'dd.mm.yyyy hh24:mi') from dual union all
  5     select 4, to_date('08.03.2023 17:00', 'dd.mm.yyyy hh24:mi'), to_date('08.03.2023 20:57', 'dd.mm.yyyy hh24:mi') from dual
  6    ),

Query:

  7  temp as
  8    (select id, date_from, date_to,
  9            numtodsinterval(date_to - date_from, 'day') diff
 10     from test
 11    )
 12  select id,
 13         to_char(date_from, 'hh24:mi') date_from,
 14         to_char(date_to  , 'hh24:mi') date_to,
 15         --
 16         diff,
 17         extract(hour from diff) hh,
 18         extract(minute from diff) mi,
 19         --
 20         case when extract(minute from diff) between 15 and 22 then 0.25
 21              when extract(minute from diff) between 23 and 37 then 0.50
 22              when extract(minute from diff) between 38 and 52 then 0.75
 23              when extract(minute from diff) < 14              then 0.00
 24              when extract(minute from diff) > 52              then 1.00
 25         end rnd,
 26         --
 27         extract(hour from diff) +
 28         case when extract(minute from diff) between 15 and 22 then 0.25
 29              when extract(minute from diff) between 23 and 37 then 0.50
 30              when extract(minute from diff) between 38 and 52 then 0.75
 31              when extract(minute from diff) < 14              then 0.00
 32              when extract(minute from diff) > 52              then 1.00
 33         end result
 34  from temp
 35  order by id;

 ID DATE_FROM  DATE_TO    DIFF                            HH  MI   RND     RESULT
--- ---------- ---------- ------------------------------ --- --- ----- ----------
  1 17:00      20:25      +000000000 03:25:00.000000000    3  25  0,50        3,5
  2 17:00      20:42      +000000000 03:42:00.000000000    3  42  0,75       3,75
  3 17:00      20:06      +000000000 03:06:00.000000000    3   6  0,00          3
  4 17:00      20:57      +000000000 03:57:00.000000000    3  57  1,00          4

SQL>

Formula to use to convert the hours entered to 1/4th hour

3 Answers3