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!

Similar Question: http://stackoverflow.com/questions/366603/mysql-sql-group-by-date-only-on-a-datetime-column — Mukesh Chapagain, Jun 14 '12 at 07:47

score 5 · Answer 1 · answered Apr 22 '10 at 16:57

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 '10 at 16:57

Salil

score 3 · Accepted Answer · answered Apr 22 '10 at 16:55

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 '10 at 16:55

hsz

2 Answers2