SQL & PL/SQL

Hi Guys,

Hope you are doing well.

I am facing some problems while extracting hours from date field. Below is an example of my orders table:-

select * from orders;

Order_NO Arrival Time Product Name
1 20-NOV-10 10:10:00 AM Desktop
2 21-NOV-10 17:26:34 PM Laptop
3 22-JAN-11 08:10:00 AM Printer

Earlier there was a requirement that daily how many orders are taking place in the order's table, In that I used to write a query

arrival_time>=trunc((sysdate-1),'DD')
and arrival_time<trunc((sysdate),'DD')

The above query gives me yesterday how many orders have been taken place.

Now I have new requirement to generate a report on every 4 hours how many orders will take place. For an example if current time is 8.00 AM IST then the query should fetch from 4.00 AM till 8 AM how many orders taken place. The report will run next at 12.00 PM IST which will give me order took place from 8.00 AM till 12.00 PM.

The report will run at every 4 hours a day and generate report of orders taken place of last 4 hours. I have a scheduler which will run this query every hours, but how to make the query understand to fetch order details which arrived last 4 hours. I am not able to achieve this using trunc.

Can you please assist me how to make this happen. I have checked "Extract" also but I am not satisfied.

Please help.

Thanks In Advance
Arijit