sql - 如何在sqlite中获取数据库中最近的日期时间

Question

数据库像这样存储时间和值(value)。

    datetimes            val
2018-09-04 11:02:15     0.24
2018-09-04 11:55:24     0.29
2018-09-04 12:01:15     0.31
2018-09-04 12:40:55     0.40
2018-09-04 13:45:23     0.49
2018-09-04 13:55:26     0.51
2018-09-04 14:20:58     0.65

我想获得每小时最近时间的 val 和日期时间。

示例 datetimes=2018-09-04 11:02:15 最接近 11:00:00，所以我得到 (2018-09-04 11:02:15, 0.24)

我知道如何获得最近的时间。

SELECT *
FROM ValueRecord
ORDER BY abs(strftime('%s','2018-09-04 13:00:00') - strftime('%s', datetimes))
LIMIT 1;

但它只返回一条记录。

我想接收所有符合条件的记录

这可能是我在示例数据中想要的结果

    datetimes            val
2018-09-04 11:02:15     0.24   // nearest 11:00:00
2018-09-04 12:01:15     0.31   // nearest 12:00:00
2018-09-04 12:40:55     0.40   // nearest 13:00:00
2018-09-04 13:55:26     0.51   // nearest 14:00:00

是否可以在 SQLite 中使用 SQL？如果是这样，我该怎么做？

或者我应该用外部代码来做吗？

Answer 1

这就是我想出的:

SELECT *
FROM ValueRecord AS VR1
WHERE NOT EXISTS (
  SELECT 1 
  FROM ValueRecord AS VR2
  WHERE (
       ( CAST(strftime('%H', VR1.datetimes) AS INTEGER) = CAST(strftime('%H', VR2.datetimes) AS INTEGER)
     AND CAST(strftime('%M', VR1.datetimes) AS INTEGER) < 30 
     AND CAST(strftime('%M', VR2.datetimes) AS INTEGER) < 30 )
  OR 
       ( CAST(strftime('%H', VR1.datetimes) AS INTEGER) = CAST(strftime('%H', VR2.datetimes) AS INTEGER) - 1
     AND CAST(strftime('%M', VR1.datetimes) AS INTEGER) > 29 
     AND CAST(strftime('%M', VR2.datetimes) AS INTEGER) < 30 )
  OR   
       ( CAST(strftime('%H', VR1.datetimes) AS INTEGER) = CAST(strftime('%H', VR2.datetimes) AS INTEGER) + 1
     AND CAST(strftime('%M', VR2.datetimes) AS INTEGER) > 29 
     AND CAST(strftime('%M', VR1.datetimes) AS INTEGER) < 30 ) 
        )
  AND ABS(CASE WHEN CAST(strftime('%M', VR2.datetimes) AS INTEGER) > 29 THEN 3600 ELSE 0 END -
         (CAST(strftime('%M', VR2.datetimes) AS INTEGER) * 60 + CAST(strftime('%S', VR2.datetimes) AS INTEGER)))
      <
      ABS(CASE WHEN CAST(strftime('%M', VR1.datetimes) AS INTEGER) > 29 THEN 3600 ELSE 0 END -
         (CAST(strftime('%M', VR1.datetimes) AS INTEGER) * 60 + CAST(strftime('%S', VR1.datetimes) AS INTEGER))) 
  ) 
ORDER BY datetimes
LIMIT 10;

注意结果:

datetimes           val
2018-09-04 11:02:15 0.24
2018-09-04 12:01:15 0.31
2018-09-04 12:40:55 0.4
2018-09-04 13:45:23 0.49
2018-09-04 13:55:26 0.51

与您的不同，因为它包括被认为最接近下一小时的行。

在这里工作: http://sqlfiddle.com/#!5/11522/42/0