Group by an individual timeframe

Question

I would like to group rows of a table by an individual time frame.

As an example let's imagine we have a list of departures at an airport:

| Departure           | Flight | Destination |
| 2016-06-01 10:12:00 | LH1234 | New York    |
| 2016-06-02 14:23:00 | LH1235 | Berlin      |
| 2016-06-02 14:30:00 | LH1236 | Tokio       |
| 2016-06-03 18:45:00 | LH1237 | Belgrad     |
| 2016-06-04 04:10:00 | LH1237 | Rio         |
| 2016-06-04 06:20:00 | LH1237 | Paris       |

I can easily group the data by full hours (days, weeks, ...) using the following query:

select to_char(departure, 'HH24') as "full hour", count(*) as "number flights"
from departures
group by to_char(departure, 'HH24')

This should result in the following table.

| full hour | number flights |
| 04        | 1              |
| 06        | 1              |
| 10        | 1              |
| 14        | 2              |
| 18        | 1              |

Now my question: Is there an elegant way (or best practise) to group data by an individual time frame. The result I'm looking for is the following:

| time frame                           | number flights |
| 2016-05-31 22:00 - 2016-06-01 06:00  | 0              |
| 2016-06-01 06:00 - 2016-06-01 14:00  | 1              |
| 2016-06-01 14:00 - 2016-06-01 22:00  | 0              |
| 2016-06-01 22:00 - 2016-06-02 06:00  | 0              |
| 2016-06-02 06:00 - 2016-06-02 14:00  | 0              |
| 2016-06-02 14:00 - 2016-06-02 22:00  | 2              |
| 2016-06-02 22:00 - 2016-06-03 06:00  | 0              |
| 2016-06-03 06:00 - 2016-06-03 14:00  | 0              |
| 2016-06-03 14:00 - 2016-06-03 22:00  | 1              |
| 2016-06-03 22:00 - 2016-06-04 06:00  | 1              |
| 2016-06-04 06:00 - 2016-06-04 14:00  | 1              |
| 2016-06-04 14:00 - 2016-06-04 22:00  | 0              |
| 2016-06-04 22:00 - 2016-06-05 06:00  | 0              |

(The rows with 0 flights aren't relevant. They are just there for a better visualization of the problem.)

Thanks for your answers in advance. :-) Peter

Answer 1

Something like this should work. Please note the two input variables, first_time and timespan. The timespan is whatever you want it to be (I wrote it in the form 8/24 for eight hours; if you make timespan into a bind variable as a number expressed in HOURS, you need the division by 24). Due to the way I wrote the formulas, there are NO requirements on first_time other than it should be one of your boundary date/times; it may even be in the future, it won't change the results. It may also be made into a bind variable, then you can decide in what format you want it to be made available to the query.

with timetable (departure, flight, destination) as (
       select to_date('2016-06-01 10:12:00', 'yyyy-mm-dd hh24:mi:ss'), 'LH1234', 'New York' 
         from dual      union all
       select to_date('2016-06-02 14:23:00', 'yyyy-mm-dd hh24:mi:ss'), 'LH1235', 'Berlin'   
         from dual      union all
       select to_date('2016-06-02 14:30:00', 'yyyy-mm-dd hh24:mi:ss'), 'LH1236', 'Tokyo'    
         from dual      union all
       select to_date('2016-06-03 18:45:00', 'yyyy-mm-dd hh24:mi:ss'), 'LH1237', 'Belgrad'  
         from dual      union all
       select to_date('2016-06-04 04:10:00', 'yyyy-mm-dd hh24:mi:ss'), 'LH1237', 'Rio'
         from dual      union all
       select to_date('2016-06-04 06:20:00', 'yyyy-mm-dd hh24:mi:ss'), 'LH1237', 'Paris'
         from dual
     ),
     input_values (first_time, timespan) as (
       select to_date('2010-01-01 06:00:00', 'yyyy-mm-dd hh24:mi:ss'), 8/24 from dual
     ),
     prep (adj_departure, flight, destination) as (
       select first_time + timespan * floor((departure - first_time) / timespan), 
              flight, destination
       from   timetable, input_values
     )
select to_char(adj_departure, 'yyyy-mm-dd hh24:mi:ss') || ' - ' ||
             to_char(adj_departure + timespan, 'yyyy-mm-dd hh24:mi:ss') as time_interval,
       count(*) as ct
from   prep, input_values
group by adj_departure, timespan
order by adj_departure
;

Output:

TIME_INTERVAL                                     CT
----------------------------------------- ----------
2016-06-01 06:00:00 - 2016-06-01 14:00:00          1
2016-06-02 14:00:00 - 2016-06-02 22:00:00          2
2016-06-03 14:00:00 - 2016-06-03 22:00:00          1
2016-06-03 22:00:00 - 2016-06-04 06:00:00          1
2016-06-04 06:00:00 - 2016-06-04 14:00:00          1   

Answer 2

Since you have groups starting at 22:00 and multiples of 8 hours afterwards then you can use TRUNC() and an offset of 2 hours to get the results grouped by each day.

You can then work out the which third of the day the departure is in and also group by that:

GROUP BY TRUNC( Departure + 2/24 ),
         FLOOR( ( Departure + 2/24 - TRUNC( Departure + 2/24 ) ) * 3 )