MySQL group by date and convert from unix timestam

问题:

I have the following sql table (simplified)

ID (int)  title (text)            date (int)
--------------------------------------------
1         Hello World             1378148920
2         Hello World2            1378182183
3         Hello World3            1378129838
4         Hello World4            1378146160
5         Hello World5            1378138038
....

The table has thousands of entries.

I wan't to ask you if it is possible to build a SQL query that groups all the posts by date but only as day.

So at the end I wan't to build a graph like this (for the last 5 days):

02.09.2013: 13 posts
01.09.2013: 14 posts

NOTE: the timestamp aren't real (they are all from today)

回答1:

You can use from-unixtime()

select FROM_UNIXTIME(`date`, '%d.%m.%Y') as ndate,
       count(id) as post_count
from your_table
group by ndate