mysql - 查询以选择当前和之前的小时、日期和月份

Question

我的sql不太好，所以我想知道这个语句对于选择当前和之前的小时、日期和月份是否正确？

DATE_SUB(NOW(), INTERVAL '1' HOUR) <= ADDTIME(Datestamp, Timestamp)
DATE_SUB(NOW(), INTERVAL '1' DAY) <= ADDTIME(Datestamp, Timestamp)
DATE_SUB(NOW(), INTERVAL '1' MONTH) <= ADDTIME(Datestamp, Timestamp)

例如，如果现在是 14:49，那么我需要计算:

1. 全部从 14:00 到 15:00(或直到 14:49)- DATE_SUB(NOW(), INTERVAL '0' HOUR) <= ADDTIME(Datestamp, Timestamp)
2. 全部从 13:00 到 14:00 DATE_SUB(NOW(), INTERVAL '1' HOUR) <= ADDTIME(Datestamp, Timestamp)

同样来自day和moth，例如如果当前是2013-01-08，那么我需要计算2013-01-08和2013-01-07

Answer 1

这是一个如何使用 MySQL date functions 的示例创建日期:

SELECT 
    NOW() AS CurrentDateTime, 
    DATE_FORMAT(NOW(), '%Y-%m-%d %H:00:00') AS CurrentHour, 
    DATE_FORMAT(NOW(), '%Y-%m-%d %H:00:00') - INTERVAL 1 HOUR AS PreviousHour, 
    CURDATE() AS CurrentDate, 
    CURDATE() - INTERVAL 1 DAY AS PreviousDate, 
    DATE_FORMAT(NOW(), '%Y-%m-01') AS CurrentMonth, 
    DATE_FORMAT(NOW(), '%Y-%m-01') - INTERVAL 1 MONTH AS PreviousMonth

输出:

CurrentDateTime: 2013-01-08 21:00:51
    CurrentHour: 2013-01-08 21:00:00
   PreviousHour: 2013-01-08 20:00:00
    CurrentDate: 2013-01-08
   PreviousDate: 2013-01-07
   CurrentMonth: 2013-01-01
  PreviousMonth: 2012-12-01

这里是示例用法:

-- SELECT PREVIOUS HOUR ROWS
SELECT *
FROM table1
WHERE ADDTIME(Datestamp, Timestamp) >= DATE_FORMAT(NOW(), '%Y-%m-%d %H:00:00') - INTERVAL 1 HOUR
AND   ADDTIME(Datestamp, Timestamp) <  DATE_FORMAT(NOW(), '%Y-%m-%d %H:00:00')

-- SELECT CURRENT HOUR ROWS
SELECT *
FROM table1
WHERE ADDTIME(Datestamp, Timestamp) >= DATE_FORMAT(NOW(), '%Y-%m-%d %H:00:00')

其他情况也是如此。