count rows before time

i have the following situation. every row has a timestamp when it was written on table. now i want to evaluate per day how many rows have been inserted before 5 am and how many after. how can that be done??

Solution

You can use the HH24 format to get the hour in 24-hour time:

select trunc(created_Date) as the_day
       ,sum(case when to_number(to_char(created_Date,'HH24')) < 5 then 1 else 0 end) as before_five
       ,sum(case when to_number(to_char(created_Date,'HH24')) >= 5 then 1 else 0 end) as after_five
from yourtable
group by trunc(created_Date)

Per USER's comment on 5:10, to show timestamps just before and after 5:

select trunc(created_Date) as the_day
       ,sum(case when to_number(to_char(created_Date,'HH24')) < 5 then 1 else 0 end) as before_five
       ,sum(case when to_number(to_char(created_Date,'HH24')) >= 5 then 1 else 0 end) as after_five
from (
   -- one row januar 1 just after 5:00 a.m.
   select to_Date('01/01/2015 05:10:12','dd/mm/yyyy hh24:mi:ss') as created_date from dual
   union all
   -- one row Januar 2 just before 5:00 a.m.
   select to_Date('02/01/2015 04:59:12','dd/mm/yyyy hh24:mi:ss') as created_date from dual
   )
group by trunc(created_Date);

THE_DAY,    BEFORE_FIVE,  AFTER_FIVE
02/01/2015, 1,            0
01/01/2015, 0,            1