[SOLVED] Oracle rounding down sysdate to the nearest minute point that divisible by 30

Oracle rounding down sysdate to the nearest minute point that divisible by 30

I have to convert sysdate by rounding down to nearest minute point that divisible by 30. For example:

If sysdate is between 2020-10-14 09:00:00 and 2020-10-14 09:29:59 then return 2020-10-14 09:00:00
If sysdate is between 2020-10-14 09:30:00 and 2020-10-14 09:59:59 then return 2020-10-14 09:30:00

How can I get my expected result in Oracle?

Solution

The minutes logic here

get the minutes
divide by 30 and truncate (which gives 0 or 1)
*30/1440 to get 0 or 30minutes of a day

and then add to the hour of day

SQL> with d as
  2   ( select to_date('09:27','HH:MI') x from dual
  3     union all
  4     select to_date('09:37','HH:MI') x from dual
  5   )
  6  select x, trunc(x,'HH') + 30*trunc(to_number(to_char(x,'MI'))/30)/1440
  7  from d;

X                   TRUNC(X,'HH')+30*TR
------------------- -------------------
01/10/2020 09:27:00 01/10/2020 09:00:00
01/10/2020 09:37:00 01/10/2020 09:30:00