我希望这个表在 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
(这些不是所有条目(
对于穿越午夜的特殊情况,请尝试以下操作:
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)
@Sloan的答案很好,但这是使用联合的另一种选择
SELECT `measurementtime`,
`measurementvalue`
FROM (SELECT `measurementtime`,
`measurementvalue`
FROM `usermeasurements`
ORDER BY measurementtime DESC
LIMIT 30) AS temp1
WHERE Hour(measurementtime) between '16:00:00'
AND '23:59:59'
union
SELECT `measurementtime`,
`measurementvalue`
FROM (SELECT `measurementtime`,
`measurementvalue`
FROM `usermeasurements`
ORDER BY measurementtime DESC
LIMIT 30) AS temp1
WHERE Hour(measurementtime) between '0:0:0'
AND '04:59:59'
ORDER BY Hour(measurementtime)
这里有一个 sqlfiddle 可以尝试使用您在问题中发布的数据。(当然,我必须从 are 部分中删除用户 ID 才能使其正常工作。