获取 MySQL 中时间戳截止后的所有行
我正在尝试从过去 30 天内具有 UNIX 时间戳的表中获取所有行。
SELECT count(member_id), DATE(FROM_UNIXTIME(`join_date`)) as date
FROM members
WHERE join_date > (join_date-'2592000')
GROUP BY DATE(FROM_UNIXTIME(`join_date`))
ORDER BY join_date DESC
显然，还有更多的事情发生，因为它按天对它们进行分组并将它们转换为 mysql 时间，但重要的是 where 子句不起作用.. 其余的都很好。
我没有收到任何错误，但查询没有按预期中断。
编辑：明白了。
SELECT count(member_id), DATE(FROM_UNIXTIME(`join_date`)) as date
FROM exp_members
WHERE DATEDIFF(CURDATE(), FROM_UNIXTIME(join_date)) < 30
GROUP BY DATE(FROM_UNIXTIME(`join_date`))
ORDER BY join_date DESC
I'm trying to fetch all rows from a table with a UNIX timestamp in the past 30 days.
SELECT count(member_id), DATE(FROM_UNIXTIME(`join_date`)) as date
FROM members
WHERE join_date > (join_date-'2592000')
GROUP BY DATE(FROM_UNIXTIME(`join_date`))
ORDER BY join_date DESC
There's more going on there, obviously, as it groups them by day and converts them to a mysql time, but the important thing is the where clause that's not working.. The rest of it is good.
I'm not getting any errors, but the query isn't cutting off as expected.
EDIT: Got it.
SELECT count(member_id), DATE(FROM_UNIXTIME(`join_date`)) as date
FROM exp_members
WHERE DATEDIFF(CURDATE(), FROM_UNIXTIME(join_date)) < 30
GROUP BY DATE(FROM_UNIXTIME(`join_date`))
ORDER BY join_date DESC
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
这个：
WHERE join_date > (join_date-'2592000')几乎肯定是您的问题。您可能想使用
DATEDIFF将 join_date 与CURDATE（而不是它本身-x）进行比较，这样您就可以使用全天增量而不是秒作为 259200，这对大多数人来说更好 -可读性。This:
WHERE join_date > (join_date-'2592000')is almost certainly your issue.You probably want to compare join_date to
CURDATE(rather than itself-x) usingDATEDIFFthat way you can use whole day increments instead of seconds as 259200 which is better most human-readable-wise.