1

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.

Abhijeet Kale
  • 1,656
  • 1
  • 16
  • 34
Martin.Z
  • 11
  • 1
  • Hi! Welcome to StackOverflow. Please take some time to see the [how to ask](http://stackoverflow.com/help/how-to-ask) section. SO is not a code delivery service, so please start on your side and ask back once you get stuck in a specific problem! – jkalden Nov 02 '15 at 12:16
  • i will learn the policy. thanks for the information – Martin.Z Nov 03 '15 at 05:32

1 Answers1

5

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
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thanks a lot. it does help, i used the trunc functions to get a start point and an end point, which gives me the time period on demand. it's really helpful. I have tried many date and time functions,but not in the right way as you suggested. – Martin.Z Nov 03 '15 at 06:27