MySQL 按日期分组并从 unix 时间戳转换

2022-01-13 00:00:00 timestamp php mysql unix-timestamp

我有如下sql表(简体)

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 ....

该表有数千个条目.

我不想问你是否可以构建一个 SQL 查询，将所有帖子按日期分组，但仅按天分组.

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.

所以最后我不想构建这样的图表(过去 5 天):

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)

推荐答案

你可以使用from-unixtime()

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

相关文章