MySQL select using datetime, group by date only

Question

Is is possible to select a datetime field from a MySQL table and group by the date only?

I'm trying to output a list of events that happen at multiple times, grouped by the date it happened on.

My table/data looks like this: (the timestamp is a datetime field)

1. 2010-03-21 18:00:00  Event1
2. 2010-03-21 18:30:00  Event2
3. 2010-03-30 13:00:00  Event3
4. 2010-03-30 14:00:00  Event4

I want to output something like this:

March 21st
1800 - Event 1
1830 - Event 2

March 30th
1300 - Event 3
1400 - Event 4

Thanks!

Salil · Accepted Answer · 2010-04-22 16:57:22Z

5

select date_format(created_at, "%Y-m-%d") as date from tablename GROUP BY date

OR

SELECT DATE_FORMAT(date_column, '%H%i') as time, event FROM table ORDER BY DATE_FORMAT(date_column, '%Y-%m-%d'), time

answered Apr 22, 2010 at 16:57

Salil

47.7k22 gold badges127 silver badges161 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

hsz · Accepted Answer · 2010-04-22 16:55:54Z

3

SELECT DATE_FORMAT(date_column, '%H%i'), DATE_FORMAT(date_column, '%M %D'), event FROM table ORDER BY date_column

%H%i - 1830

%M%D - March 21st

answered Apr 22, 2010 at 16:55

hsz

153k63 gold badges268 silver badges320 bronze badges