Rounding Oracle SYSDATE to the nearest hour

Question

Supposing the current SYSDATE is 03-APR-17 03.35.49, my query would need to get values between:

a. SYSDATE rounded to nearest passed hour (in this case 03.00.00)

b. a minus 1 hour = 02.00.00

I have tried and found that using "-1/24" returns the date time with an hour subtracted. Kinda new to Oracle so struggling on the first part. Not sure if I should be using ROUND or FLOOR or any other functionality. Any help appreciated.

Answer 1

select sysdate,
       trunc(sysdate, 'hh') as truncated,
       round(sysdate, 'hh') as rounded
from   dual;

SYSDATE              TRUNCATED            ROUNDED
-------------------  -------------------  -------------------
04/03/2017 15:54:22  04/03/2017 15:00:00  04/03/2017 16:00:00

Answer 2

This is to truncate the value:

select trunc(sysdate, 'hh')
from dual;

To round it:

select trunc(sysdate + interval '30' minute, 'hh')
from dual;

Or:

select round(sysdate, 'hh')
from dual;

I forget that round() can also take a format argument.