MySQL 从时间戳开始按天分组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5970938/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Group by day from timestamp
提问by DJafari
In my poststable, it saves timestamp for each post record.
在我的posts表中，它为每个帖子记录保存时间戳。
What query can group posts by day, using this timestamp column?
什么查询可以使用此时间戳列按天对帖子进行分组？
回答by p.campbell
How about this? Using the DATEfunction:
这个怎么样？使用DATE功能：
SELECT DATE(FROM_UNIXTIME(MyTimestamp)) AS ForDate,
COUNT(*) AS NumPosts
FROM MyPostsTable
GROUP BY DATE(FROM_UNIXTIME(MyTimestamp))
ORDER BY ForDate
This will show you the number of posts on each date that the table has data for.
这将向您显示该表具有数据的每个日期的帖子数。
回答by Gerardo Lagger
SELECT DATE(timestamp) AS ForDate,
COUNT(*) AS NumPosts
FROM user_messages
GROUP BY DATE(timestamp)
ORDER BY ForDate
This found for me. I have timestamp like "2013-03-27 15:46:08".
这是为我找到的。我有像“2013-03-27 15:46:08”这样的时间戳。
回答by MatBailie
SELECT
*
FROM
(
SELECT DATE(FROM_UNIXTIME(MyTimestamp)) AS ForDate,
ROW_NUMBER() OVER (PARTITION BY DATE(FROM_UNIXTIME(MyTimestamp)) ORDER BY MyTimeStamp) AS PostRowID,
*
FROM MyPostsTable
)
AS sequenced_daily_posts
WHERE
ForDate = <whatever date(s) you want>
AND PostRowID <= 2
