午夜之前和之后的时间之间的Mysql

Question

我希望这张表以 24 小时制返回这些时间之间的时间。但它什么也没返回。

SELECT `measurementtime`, 
       `measurementvalue` 
FROM   (SELECT `measurementtime`, 
               `measurementvalue` 
        FROM   `usermeasurements` 
        WHERE  userid = 1 
        ORDER  BY measurementtime DESC 
        LIMIT  30) AS temp1 
WHERE  Hour(measurementtime) >= '16:00:00' 
       AND Hour(measurementtime) <= '04:59:59' 
ORDER  BY Hour(measurementtime) 

同时这完美地工作

SELECT `measurementtime`, 
       `measurementvalue` 
FROM   (SELECT `measurementtime`, 
               `measurementvalue` 
        FROM   `usermeasurements` 
        WHERE  userid = 1 
        ORDER  BY measurementtime DESC 
        LIMIT  30) AS temp2 
WHERE  ( Hour(measurementtime) BETWEEN '05:00:00' AND '11:59:59' ) 
ORDER  BY Hour(measurementtime) 

所以问题似乎是我要查找两个不同日期之间的时间。又怎样 ？我需要像那样保留这些时间段，而且我不想处理日期。

条目是这样的

        MeasurementTime     MeasurementValue    UserMeasurementID   
    2017-07-11 17:00:00         123                     114 
    2017-07-11 17:00:00         123                     115 
    2017-07-10 20:00:00         12                      116 
    2017-03-15 16:31:00         3                       113 
    2017-03-03 01:03:00         12                      112 
    2016-12-28 15:05:15         39                      109 
    2016-12-19 15:10:29         6                       107 

(这些还不是全部)

Answer 1

对于穿越午夜的特殊情况，试试这个:

SELECT `measurementtime`, 
       `measurementvalue` 
FROM   (SELECT `measurementtime`, 
               `measurementvalue` 
        FROM   `usermeasurements` 
        WHERE  userid = 1 
        ORDER  BY measurementtime DESC 
        LIMIT  30) AS temp1 
WHERE  HOUR(measurementtime) >= 16
       OR HOUR(measurementtime) BETWEEN 0 AND 4
ORDER  BY HOUR(measurementtime)

如果您可以在带有变量的存储过程中执行此操作，则更通用的版本可能是:

SET @range_start = 16;
SET @range_end = 4;

SELECT 
    `measurementtime`, 
    `measurementvalue` 
FROM   (SELECT `measurementtime`, 
            `measurementvalue` 
        FROM   `usermeasurements` 
        WHERE  userid = 1 
        ORDER  BY measurementtime DESC 
        LIMIT  30) AS temp1 
WHERE 
    IF(@range_start > @range_end,
        HOUR(measurementtime) >= 16
        OR HOUR(measurementtime) < 4,
        measurementtime BETWEEN @range_start AND @range_end
ORDER BY HOUR(measurementtime)