2

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                |
  • 1
    At first you need a number table, 59 for minutes,than you use SUBSTRING_INDEX to select the HOUR part from start and do a CROSS JOIN to get all minutes for each hour then for number of visits you check in the start and end column by hour and minute to see if it belongs in the interval.Yes it`s a big mess. – Mihai Oct 30 '14 at 22:14
  • Also are your fields VARCHAR or something else? – Mihai Oct 30 '14 at 22:19
  • They are DATETIMEs. – jSherz Oct 31 '14 at 12:36
3

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 >= starttime and the_time <= 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> use test
Database changed
mysql> drop table if exists visit;
Query OK, 0 rows affected (0.00 sec)

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

mysql> 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');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql>

PROPOSED QUERY EXECUTED

mysql> 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 >= starttime and the_time <= endtime
    -> 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>

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 >= starttime and the_time <= endtime
group by the_time with rollup;

EXECUTED...

mysql> 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 >= starttime and the_time <= endtime
    -> 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>

GIVE IT A TRY !!!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.