# How to count concurrently bookings in sql in time interval per minute?

If I have a start and stop time for a booking, how can I calculate the number of bookings there are each minute? I made a simplified version of my database table looks like here:

```Start time        | End time         | booking   |
--------------------------------------------------
2020-09-01 10:00  | 2020-09-01 10:10 | Booking 1 |
2020-09-01 10:00  | 2020-09-01 10:05 | Booking 2 |
2020-09-01 10:05  | 2020-09-01 10:10 | Booking 3 |
2020-09-01 10:09  | 2020-09-01 10:10 | Booking 4 |
```

I want to have the bookings between a given time interval like 10:02 – 10:09. It should be something like this as result:

## Desired result

```Time  | count
-----------
10:02 | 2 |
10:03 | 2 |
10:04 | 2 |
10:05 | 3 |
10:06 | 2 |
10:07 | 2 |
10:08 | 2 |
10:09 | 3 |
```

## Question

How can this be achieved? Today I export it to python however I think it should be possible to achieve directly in SQL.

You can use a recursive CTE directly on your data:

```with recursive cte as (
select start_time, end_time
from t
union all
select start_time + interval 1 minute, end_time
from cte
where start_time < end_time
)
select start_time, count(*)
from cte
group by start_time
order by start_time;
```

Here is a db<>fiddle.

EDIT:

In earlier versions of MySQL, it helps to have a tally table. You can create one on the fly, using something like:

```(select @rn := @rn + 1 as n
from t cross join
(select @rn := 0) params
) tally
```

You need enough numbers for your maximum span, but then you can do:

```select t.start_time + interval tally.n hour, count(*)
from t join
(select @rn := @rn + 1 as n
from t cross join
(select @rn := -1) params  -- so it starts from 0
limit 100
) tally
on t.start_time + interval tally.n hour <= t.end_time
group by t.start_time + interval tally.n hour;
```
Source: stackoverflow
The answers/resolutions are collected from stackoverflow, are licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0 .