I have a table where I store parsed server logs:
mysql> SELECT id,date,message_type FROM log_entry LIMIT 10 ;
+----+---------------------+--------------+
| id | date | message_type |
+----+---------------------+--------------+
| 1 | 2015-01-08 18:13:15 | 2 |
| 2 | 2015-01-08 18:13:14 | 1 |
| 3 | 2015-01-08 18:13:12 | 1 |
| 4 | 2015-01-08 18:13:11 | 1 |
| 5 | 2015-01-08 18:13:11 | 1 |
| 6 | 2015-01-08 18:13:11 | 1 |
| 7 | 2015-01-08 18:13:10 | 1 |
| 8 | 2015-01-08 18:13:08 | 1 |
| 9 | 2015-01-08 18:13:07 | 1 |
| 10 | 2015-01-08 18:13:06 | 512 |
+----+---------------------+--------------+
Each log is classified by its message_type. I can also group them by hour:
mysql> select DAY(date) as day, HOUR(date) as hour, message_type, count(message_type) FROM log_entry WHERE date >= CURDATE() - INTERVAL 2 MONTH AND message_type BETWEEN 1 AND 4 GROUP BY day,hour,message_type ORDER BY date ASC LIMIT 3;
+------+------+--------------+---------------------+
| day | hour | message_type | count(message_type) |
+------+------+--------------+---------------------+
| 8 | 18 | 1 | 177 |
| 8 | 18 | 2 | 61 |
| 8 | 18 | 4 | 14 |
+------+------+--------------+---------------------+
In this example, on the 8th day and the 18th hour of a certain date, I have 177 entries with a message_type of 1, 61 with type 2 and 14 with type 4.
I would like to get the following calculation for every hour:
type(1) - type(2) - type(4)
Which in this case would return
+------+------+--------------+---------------------+
| day | hour | result |
+------+------+--------------+---------------------+
| 8 | 18 | 102 |
+------+------+--------------+---------------------+
I'm doing this is because I want to create a chart showing hourly activity on a server I'm running. A type 1 entry means someone connected, a type 2 means there was a graceful disconnect and a type 4 means there was a timeout.
Is this a feasible thing to do in the database or should I do this on the application layer?
You can use a SUM() function and CASE statement to count each message_type and do the math.
SELECT DAY(date) as day, HOUR(date) as hour,
SUM(CASE WHEN message_type = 1 THEN 1 ELSE 0 END) -
SUM(CASE WHEN message_type = 2 THEN 1 ELSE 0 END) -
SUM(CASE WHEN message_type = 4 THEN 1 ELSE 0 END) as result
FROM log_entry
WHERE date >= CURDATE() - INTERVAL 2 MONTH
AND message_type BETWEEN 1 AND 4
GROUP BY day,hour;