how to group results according to a timestamp interval

2
votes

I have a table that holds timestamped messages from a sender to a receiver, and it looks like:

mes     timestamp               sender  receiver
10      2014-04-13 12:22:25.000 1       72
10      2014-04-13 12:22:25.000 1       91
10      2014-04-13 12:22:25.000 1       58
16      2014-02-20 20:09:06.000 3       35
16      2014-02-20 20:09:06.000 3       54
17      2014-03-05 14:55:28.000 1       65
18      2014-03-07 14:55:28.000 2       97
19      2014-03-09 14:55:28.000 2       97

My table holds 3 millions rows like these, and I am trying to group results according to timestamp intervals, counting the number of messages in each month between each pair of sender-receiver. Something like:

timestamp sender  receiver count
2014-04   1       72       1
2014-04   1       91       1
2014-04   1       58       1
2014-02   3       35       1
2014-02   3       54       1
2014-03   1       65       1
2014-03   2       97       2

I really have no clue how to specify my clause in mysql, so I apologize for not providing a snippet of my not-working code... should I use something as a switch control and manually specify the time interval? or is there something more specific in mysql to manage tasks like this?

1
mysql

1 Answers

2
votes

This is fairly straight-forward GROUP BY with multiple levels:

SELECT CONCAT(YEAR(tstamp),'-',MONTH(tstamp)) as tstamp, sender, receiver, COUNT(*) AS cnt
FROM yourtable
GROUP BY YEAR(tstamp), MONTH(tstamp),sender,receiver;

I'm using tstamp as field name for "timestamp" to avoid conflicting with reserved words (timestamp).