从SQL中的时间戳，从今天，昨天，本周，本月和两个日期之间选择记录php mysql
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17198468/
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
From the timestamp in SQL, selecting records from today, yesterday, this week, this month and between two dates php mysql
提问by Steve
Hopefully, it's an easy solution, but I am trying to filter the data for the following:-
希望这是一个简单的解决方案，但我正在尝试过滤以下数据：-
- Today
- Yesterday
- This Week
- This Month
- In between two dates.
- 今天
- 昨天
- 本星期
- 这个月
- 在两个日期之间。
The date I get from the database is basically a timestamp.
我从数据库中得到的日期基本上是一个时间戳。
This is what I tried:-
这是我尝试过的：-
Today
SELECT n.title, COUNT(*) AS times FROM node_view_count WHERE timestamp > DATE_SUB(NOW(), INTERVAL 1 DAY)Yesterday
SELECT n.title, COUNT(*) AS times FROM node_view_count WHERE timestamp > DATE_SUB(NOW(), INTERVAL 7 DAYS)
今天
SELECT n.title, COUNT(*) AS times FROM node_view_count WHERE timestamp > DATE_SUB(NOW(), INTERVAL 1 DAY)昨天
SELECT n.title, COUNT(*) AS times FROM node_view_count WHERE timestamp > DATE_SUB(NOW(), INTERVAL 7 DAYS)
...
...
It's not really working.
这不是真的有效。
Any idea?
任何的想法？
回答by Ed Gibbs
If you're selecting by date only, base your calculations on CURDATE(which returns date only) rather than NOW(which returns date and time). These examples will catch all times within the day ranges:
如果您仅按日期选择，则基于CURDATE（仅返回日期）而不是NOW（返回日期和时间）进行计算。这些示例将捕获一天范围内的所有时间：
- Today:
WHERE timestamp >= CURDATE() - Yesterday:
WHERE timestamp >= DATE_SUB(CURDATE(), INTERVAL 1 DAY) AND timestamp < CURDATE() - This month:
WHERE timestamp >= DATE_SUB(CURDATE(), INTERVAL DAYOFMONTH(CURDATE())-1 DAY) - Between the two dates 3 June 2013 and 7 June 2013 (note how the end date is specified as 8 June, not 7 June):
WHERE timestamp >= '2013-06-03' AND timestamp < '2013-06-08'
- 今天：
WHERE timestamp >= CURDATE() - 昨天：
WHERE timestamp >= DATE_SUB(CURDATE(), INTERVAL 1 DAY) AND timestamp < CURDATE() - 这个月：
WHERE timestamp >= DATE_SUB(CURDATE(), INTERVAL DAYOFMONTH(CURDATE())-1 DAY) - 在 2013 年 6 月 3 日和 2013 年 6 月 7 日这两个日期之间（注意如何将结束日期指定为 6 月 8 日，而不是 6 月 7 日）：
WHERE timestamp >= '2013-06-03' AND timestamp < '2013-06-08'
The "this week" depends on which day you start your week; I'll leave that to you. You can use the DAYOFWEEKfunction to tweak CURDATE()to the proper ranges.
“本周”取决于您开始一周的哪一天；我会把它留给你。您可以使用该DAYOFWEEK功能调整CURDATE()到适当的范围。
Addendum: OP's column type was INTEGER, storing a UNIX timestamp, and my answer assumed the column type was TIMESTAMP. Here's how to do all the same things with a UNIX timestamp value and still maintain optimization if the column is indexed (as the answers above will do if the TIMESTAMPcolumn is indexed)...
附录：OP 的列类型是INTEGER，存储一个 UNIX 时间戳，我的回答假设列类型是TIMESTAMP. 以下是如何使用 UNIX 时间戳值执行所有相同的操作，并且如果该列被编入索引仍然保持优化（如果该列被编入索引，则上述答案将执行TIMESTAMP）...
Basically, the solution is to just wrap the beginning and/or ending dates in the UNIX_TIMESTAMPfunction:
基本上，解决方案是在UNIX_TIMESTAMP函数中包装开始和/或结束日期：
- Today:
WHERE timestamp >= UNIX_TIMESTAMP(CURDATE()) - Yesterday:
WHERE timestamp >= UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 1 DAY)) AND timestamp < UNIX_TIMESTAMP(CURDATE()) - This month:
WHERE timestamp >= UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL DAYOFMONTH(CURDATE())-1 DAY)) - Between the two dates 3 June 2013 and 7 June 2013 (note how the end date is specified as 8 June, not 7 June):
WHERE timestamp >= UNIX_TIMESTAMP('2013-06-03') AND timestamp < UNIX_TIMESTAMP('2013-06-08')
- 今天：
WHERE timestamp >= UNIX_TIMESTAMP(CURDATE()) - 昨天：
WHERE timestamp >= UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 1 DAY)) AND timestamp < UNIX_TIMESTAMP(CURDATE()) - 这个月：
WHERE timestamp >= UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL DAYOFMONTH(CURDATE())-1 DAY)) - 在 2013 年 6 月 3 日和 2013 年 6 月 7 日这两个日期之间（注意如何将结束日期指定为 6 月 8 日，而不是 6 月 7 日）：
WHERE timestamp >= UNIX_TIMESTAMP('2013-06-03') AND timestamp < UNIX_TIMESTAMP('2013-06-08')
