I'm trying to select rows from the last 2 hours. For some reason it doesn't work. The code syntax looks ok and works when used on other tables, but for some reason when used on table Posts, get rows a lot older than 2 hours.
我正在尝试从过去2小时中选择行。由于某种原因，它不起作用。代码语法看起来没问题，并且在其他表上使用时可以正常工作，但由于某些原因，当在表Posts上使用时，得到的行比2小时多很多。
SELECT * FROM Posts WHERE `Date` > SUBDATE( CURRENT_DATE, INTERVAL 2 HOUR)
Is there any problem with the code? Is there another way to write it? What could be the reason for this?
代码有问题吗？还有其他方法可以写吗？这可能是什么原因？
4 个解决方案
#1
7
That's because you're using CURRENT_DATE, you should use NOW() or CURRENT_TIMESTAMP instead.
那是因为你正在使用CURRENT_DATE，你应该使用NOW（）或CURRENT_TIMESTAMP。
The problem is that using CURRENT_DATE, being a date value, the time defaults to 00:00:00, so by substracting 2 hours you end up getting 22:00:00 of the previous day, instead of the last 2 hours...
问题是使用CURRENT_DATE作为日期值，时间默认为00:00:00，所以通过减去2小时你最终获得前一天的22:00:00，而不是最后2小时......
#2
17
You can use simpler notation:
您可以使用更简单的表示法：
SELECT * FROM Posts WHERE `Date` > NOW() - INTERVAL 2 HOUR
#3
6
change to this:
改为：
SELECT * FROM Posts WHERE `Date` > SUBDATE( CURRENT_TIMESTAMP, INTERVAL 2 HOUR)
#4
1
You can try this one
你可以尝试这个
SELECT * FROM Posts WHERE `Date` > DATE_SUB(CURDATE(), INTERVAL 3 HOUR)
#1
7
That's because you're using CURRENT_DATE, you should use NOW() or CURRENT_TIMESTAMP instead.
那是因为你正在使用CURRENT_DATE，你应该使用NOW（）或CURRENT_TIMESTAMP。
The problem is that using CURRENT_DATE, being a date value, the time defaults to 00:00:00, so by substracting 2 hours you end up getting 22:00:00 of the previous day, instead of the last 2 hours...
问题是使用CURRENT_DATE作为日期值，时间默认为00:00:00，所以通过减去2小时你最终获得前一天的22:00:00，而不是最后2小时......
#2
17
You can use simpler notation:
您可以使用更简单的表示法：
SELECT * FROM Posts WHERE `Date` > NOW() - INTERVAL 2 HOUR
#3
6
change to this:
改为：
SELECT * FROM Posts WHERE `Date` > SUBDATE( CURRENT_TIMESTAMP, INTERVAL 2 HOUR)
#4
1
You can try this one
你可以尝试这个
SELECT * FROM Posts WHERE `Date` > DATE_SUB(CURDATE(), INTERVAL 3 HOUR)