By hour usage query in sql

Question

Hi guys, hope you can help me.

I've a DB table, lets call it activities

[activities]
 [start_time - datetime]
 [end_time - datetime]
 [name - string]

i need a query which will return me, for each hour of a 24day how many activites were active at that time.

for example:

[time]         [usage]
11:00 (11 am)    12

etc...

Answer 1

You may want to use create another table (could be temporary) that holds the numbers 0-23 representing the hours of the day:

CREATE TABLE hours_of_day (hour int);
INSERT INTO hours_of_day VALUES (1), (2), (3), (4), (5), (6), (7), (8),
                                (9), (10), (11), (12), (13), (14), (15), 
                                (16), (17), (18), (19), (20), (21), (22), (23);

Then you should be able to use a query such as the following (using MySQL):

SELECT      hd.hour, COUNT(a.name) `usage`
FROM        hours_of_day hd
LEFT JOIN   activities a ON 
            (hd.hour BETWEEN HOUR(a.start_time) AND HOUR(a.end_time))
GROUP BY    hd.hour;

Test case:

CREATE TABLE activities (start_time datetime, 
                         end_time   datetime, 
                         name       varchar(10));

INSERT INTO activities VALUES 
     ('2010-01-01 12:10:00', '2010-01-01 13:10:00', 'b'),
     ('2010-01-01 13:20:00', '2010-01-01 13:30:00', 'c'),
     ('2010-01-01 13:50:00', '2010-01-01 14:05:00', 'd'),
     ('2010-01-01 17:20:00', '2010-01-01 20:30:00', 'e');

Result:

+------+-------+
| hour | usage |
+------+-------+
|    1 |     0 |
|    2 |     0 |
|    3 |     0 |
|    4 |     0 |
|    5 |     0 |
|    6 |     0 |
|    7 |     0 |
|    8 |     0 |
|    9 |     0 |
|   10 |     0 |
|   11 |     0 |
|   12 |     1 |
|   13 |     3 |
|   14 |     1 |
|   15 |     0 |
|   16 |     0 |
|   17 |     1 |
|   18 |     1 |
|   19 |     1 |
|   20 |     1 |
|   21 |     0 |
|   22 |     0 |
|   23 |     0 |
+------+-------+
23 rows in set (0.00 sec)

If you don't want all those 0s, you can use INNER JOIN instead of LEFT JOIN. The results we be as follows:

+------+-------+
| hour | usage |
+------+-------+
|   12 |     1 |
|   13 |     3 |
|   14 |     1 |
|   17 |     1 |
|   18 |     1 |
|   19 |     1 |
|   20 |     1 |
+------+-------+
7 rows in set (0.00 sec)