Breaking down by minutes in between two dates In MySQL, I've got a data model with a "visit" table that has start and end datetimes.

How can I get a breakdown of the number of visits that are active per minute?

e.g.

| visit_id | start | end   |
| 1        | 10:10 | 10:15 |
| 2        | 10:15 | 10:20 |
| 3        | 10:12 | 10:18 |


Would return something similar to:

| time  | number of visits |
| 10:10 | 1                |
| 10:11 | 1                |
| 10:12 | 2                |
| 10:13 | 2                |
| 10:14 | 2                |
| 10:15 | 2                |
| 10:16 | 2                |
| 10:17 | 2                |
| 10:18 | 2                |
| 10:19 | 1                |
| 10:20 | 1                |
You are going to think I am crazy but here it goes

PROPOSED QUERY

select the_time,count(1) visits from
(select the_date + interval m minute the_time from
(select '2014-10-30' the_date) AA,(select hh*60+mm m from
(select 0 hh union select  1 union select  2 union select  3 union select  4 union
select  5 union select  6 union select  7 union select  8 union select  9 union
select 10 union select 11 union select 12 union select 13 union select 14 union
select 15 union select 16 union select 17 union select 18 union select 19 union
select 20 union select 21 union select 22 union select 23) hours,(select t*10+u mm from
(select 0 t union select 1 union select 2 union select 3 union select 4 union select 5) tens,
(select 0 u union select 1 union select 2 union select 3 union select 4 union select 5 union
select 6 union select 7 union select 8 union select 9) units) minutes order by m) BB) A
inner join visit on the_time &gt;= starttime and the_time &lt;= endtime
group by the_time;


What the query does is generate every minute of a day. You inject the date in subquery AA

SAMPLE DATA

use test
drop table if exists visit;
create table visit
(
    visit_id int not null auto_increment primary key,
    starttime datetime not null,endtime datetime not null
);
insert into visit (starttime,endtime) values
('2014-10-30 10:10:00','2014-10-30 10:15:00'),
('2014-10-30 10:15:00','2014-10-30 10:20:00'),
('2014-10-30 10:12:00','2014-10-30 10:18:00');


SAMPLE DATA LOADED

mysql&gt; use test
Database changed
mysql&gt; drop table if exists visit;
Query OK, 0 rows affected (0.00 sec)

mysql&gt; create table visit
    -&gt; (
    -&gt;     visit_id int not null auto_increment primary key,
    -&gt;     starttime datetime not null,endtime datetime not null
    -&gt; );
Query OK, 0 rows affected (0.02 sec)

mysql&gt; insert into visit (starttime,endtime) values
    -&gt; ('2014-10-30 10:10:00','2014-10-30 10:15:00'),
    -&gt; ('2014-10-30 10:15:00','2014-10-30 10:20:00'),
    -&gt; ('2014-10-30 10:12:00','2014-10-30 10:18:00');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql&gt;


PROPOSED QUERY EXECUTED

mysql&gt; select the_time,count(1) visits from
    -&gt; (select the_date + interval m minute the_time from
    -&gt; (select '2014-10-30' the_date) AA,(select hh*60+mm m from
    -&gt; (select 0 hh union select  1 union select  2 union select  3 union select  4 union
    -&gt; select  5 union select  6 union select  7 union select  8 union select  9 union
    -&gt; select 10 union select 11 union select 12 union select 13 union select 14 union
    -&gt; select 15 union select 16 union select 17 union select 18 union select 19 union
    -&gt; select 20 union select 21 union select 22 union select 23) hours,(select t*10+u mm from
    -&gt; (select 0 t union select 1 union select 2 union select 3 union select 4 union select 5) tens,
    -&gt; (select 0 u union select 1 union select 2 union select 3 union select 4 union select 5 union
    -&gt; select 6 union select 7 union select 8 union select 9) units) minutes order by m) BB) A
    -&gt; inner join visit on the_time &gt;= starttime and the_time &lt;= endtime
    -&gt; group by the_time;
+---------------------+--------+
| the_time            | visits |
+---------------------+--------+
| 2014-10-30 10:10:00 |      1 |
| 2014-10-30 10:11:00 |      1 |
| 2014-10-30 10:12:00 |      2 |
| 2014-10-30 10:13:00 |      2 |
| 2014-10-30 10:14:00 |      2 |
| 2014-10-30 10:15:00 |      3 |
| 2014-10-30 10:16:00 |      2 |
| 2014-10-30 10:17:00 |      2 |
| 2014-10-30 10:18:00 |      2 |
| 2014-10-30 10:19:00 |      1 |
| 2014-10-30 10:20:00 |      1 |
+---------------------+--------+
11 rows in set (0.00 sec)

mysql&gt;


PROPOSED QUERY WITH DATE FORMATTED, TOTAL VISITS

select IFNULL(TIME(the_time),'Total Visits') the_time,
count(1) visits from
(select the_date + interval m minute the_time from
(select '2014-10-30' the_date) AA,(select hh*60+mm m from
(select 0 hh union select  1 union select  2 union select  3 union select  4 union
select  5 union select  6 union select  7 union select  8 union select  9 union
select 10 union select 11 union select 12 union select 13 union select 14 union
select 15 union select 16 union select 17 union select 18 union select 19 union
select 20 union select 21 union select 22 union select 23) hours,(select t*10+u mm from
(select 0 t union select 1 union select 2 union select 3 union select 4 union select 5) tens,
(select 0 u union select 1 union select 2 union select 3 union select 4 union select 5 union
select 6 union select 7 union select 8 union select 9) units) minutes order by m) BB) A
inner join visit on the_time &gt;= starttime and the_time &lt;= endtime
group by the_time with rollup;


EXECUTED...

mysql&gt; select IFNULL(TIME(the_time),'Total Visits') the_time,
    -&gt; count(1) visits from
    -&gt; (select the_date + interval m minute the_time from
    -&gt; (select '2014-10-30' the_date) AA,(select hh*60+mm m from
    -&gt; (select 0 hh union select  1 union select  2 union select  3 union select  4 union
    -&gt; select  5 union select  6 union select  7 union select  8 union select  9 union
    -&gt; select 10 union select 11 union select 12 union select 13 union select 14 union
    -&gt; select 15 union select 16 union select 17 union select 18 union select 19 union
    -&gt; select 20 union select 21 union select 22 union select 23) hours,(select t*10+u mm from
    -&gt; (select 0 t union select 1 union select 2 union select 3 union select 4 union select 5) tens,
    -&gt; (select 0 u union select 1 union select 2 union select 3 union select 4 union select 5 union
    -&gt; select 6 union select 7 union select 8 union select 9) units) minutes order by m) BB) A
    -&gt; inner join visit on the_time &gt;= starttime and the_time &lt;= endtime
    -&gt; group by the_time with rollup;
+--------------+--------+
| the_time     | visits |
+--------------+--------+
| 10:11:00     |      1 |
| 10:12:00     |      1 |
| 10:13:00     |      2 |
| 10:14:00     |      2 |
| 10:15:00     |      2 |
| 10:16:00     |      3 |
| 10:17:00     |      2 |
| 10:18:00     |      2 |
| 10:19:00     |      2 |
| 10:20:00     |      1 |
| 10:20:00     |      1 |
| Total Visits |     19 |
+--------------+--------+
12 rows in set, 1 warning (0.01 sec)

mysql&gt;


GIVE IT A TRY !!!