Use PL/SQL to return a time period that systimestamp currently belongs to

Question

i.e.

• I planned to divide 24 hours into 24 sections (1 hour for each section): 00:30:00 AM to 01:29:59 AM as 1st section and so on

• Current systimestamp = "02-NOV-15 06.13.49.000000000 PM" I want to get the SQLs that can return a time period from 5:30:00 PM to 6:29:59 PM, which is the time period the systimestamp currently belongs to.

I have no idea how to write this SQL as beginner, thanks for any of your inputs.

Answer 1

You can subtract half an hour, truncate the value, and then add back half an hour. Something like this:

select trunc(systimestamp - 0.5/24, 'hh') + 0.5/24

You can also use interval '30' minute:

select trunc(systimestamp - interval '30' minute, 'hh') + interval '30' minute