Selecting records in groups by date - possible?

Question

I don't think there is an elegant way to do this, but here goes. Database contains 5000 records with timestamps of 2 years. I need to pull the records under each day of the year.

So it looks like..

09/09/2009 - record938, record2, record493

09/10/2009 - record260, record485, record610

...etc

I cannot use GROUP BY. There are duplicates and that's OK. I need to show them.

Is this possible? PHP/MySQL?

One way of doing it is looping through every day of the year and doing a query with "WHERE DAY(created_at)..." but obviously this isn't elegant.

HOW can I do this? I posted this question before without a satisfactory answer (answer was what I just stated above)

Answer 1

MySQL has the group_concat() aggregate function:

SELECT date(rec_time), group_concat(rec_id)
FROM records GROUP BY date(rec_time);

Will return all rec_id values from table joined by commas, for each date. If you want a separator other than , use group_concat(some_column SEPARATOR '-')

Example

For example if your table looks like:

+--------+---------------------+
| rec_id | rec_time            |
+--------+---------------------+
|      1 | 2009-11-28 10:00:00 |
|      2 | 2009-11-28 20:00:00 |
|      3 | 2009-11-27 15:00:00 |
|      4 | 2009-11-27 07:00:00 |
|      5 | 2009-11-28 08:00:00 |
+--------+---------------------+

Then this query gives:

mysql> SELECT date(rec_time), group_concat(rec_id) 
    -> FROM records GROUP BY date(rec_time);
+----------------+----------------------+
| date(rec_time) | group_concat(rec_id) |
+----------------+----------------------+
| 2009-11-27     | 3,4                  |
| 2009-11-28     | 1,2,5                |
+----------------+----------------------+

Caveat

Beware that the result is limited by the group_concat_max_len system variable, which defaults to only 1024 bytes! To avoid hitting this wall, you should execute this before running the query:

SET SESSION group_concat_max_len = 65536;

Or more, depending on how many results you expect. But this value cannot be larger than max_allowed_packet