count rows before time

Question

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??

Answer 1

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

Answer 2

Assuming your timestamp is a DATE column:

select trunc(date_written) as day
,      count (case when (date_written-trunc(date_written))*24 < 5 then 1 end) before_5_count
,      count (case when (date_written-trunc(date_written))*24 >= 5 then 1 end) after_5_count
from   mytable
group by trunc(date_written) 

Answer 3

select to_char(time_column, 'dd/mm/yyyy'),
     sum( decode ( greatest(extract(hour from time_column), 5), extract(hour from time_column), 1, 0)) post_5,
     sum( decode ( greatest(extract(hour from time_column), 5), extract(hour from time_column), 0, 1)) pre_5
from test_time
group by to_char(time_column, 'dd/mm/yyyy')