SQLite查询获取最接近的日期时间

Question

我正在尝试编写一个SQLite语句，从用户输入（来自WPF日期选择器）获取最近的日期时间。 我有一个表IRquote(rateId, quoteDateAndTime, quoteValue)。

例如，如果用户输入10/01/2000，而数据库仅存储了08/01/2000、07/01/2000和14/01/2000的修复数据，则返回08/01/2000，作为距离10/01/2000最近的日期。

当然，我希望它不仅适用于日期，还适用于时间。

我尝试使用此查询，但它返回具有最远日期的行，而不是最接近的行：

SELECT quoteValue FROM IRquote 
WHERE rateId = '" + pRefIndexTicker + "'
ORDER BY abs(datetime(quoteDateAndTime) - datetime('" + DateTimeSQLite(pFixingDate) + "')) ASC
LIMIT 1;

注意，我有一个名为DateTimeSQLite的函数，可以将用户输入转换为正确的格式。

我不明白为什么这不起作用。 我该怎么做？谢谢你的帮助。

- MarinD

---

你从哪里获取用户输入？数据存储在哪里？ - Bernd Linde

我不确定所有的SQL命令来完成这个任务，但我怀疑肯定有一种方法可以将输入与存储的值进行比较，从中得出一个数值，按照该数值排序（ORDER BY），并取第一条记录（TOP 1）。如果需要在排序之前将其转换为绝对值，以便获取最接近的时间（无论是向前还是向后）。 - David

@BerndLinde 我编辑了我的问题以添加更多细节。David，我会尝试您提出的方法。感谢您的帮助。 - Sithered

Answer 1

为了获得最接近的日期，您需要使用SQLite函数strftime('%s', datetime)。

使用此示例/演示，您将获得最接近您给定日期的日期。
请注意，日期2015-06-25 10:00:00是用户选择的输入日期时间。

select t.ID, t.Price, t.PriceDate,
       abs(strftime('%s','2015-06-25 10:00:00') - strftime('%s', t.PriceDate)) as 'ClosestDate'
  from Test t
 order by abs(strftime('%s','2015-06-25 10:00:00') - strftime('%s', PriceDate))
 limit 1;

SQL解释：
我们使用strftime（'%s') - strftime('%s')来计算两个日期之间的差异（以秒为单位）（注意：必须是'%s'而不是'%S'）。由于这可能是正数或负数，因此我们还需要使用abs函数使其全部变成正数，以确保我们的order by和后续的limit 1部分工作正确。

- Bernd Linde

---

高度低效，将日期时间转换为字符串。 - ypercubeᵀᴹ

@ypercube SQLite将DateTime存储为字符串，因此实际上没有进行任何转换。SQLite中没有DateTime类型。 - Bernd Linde

我已经添加了一个使用列索引的查询答案（谢谢提供 fiddle，我也用到了 ;)）。 - ypercubeᵀᴹ

strftime('%s', t.PriceDate) 进行了一次转换。在列上使用函数（如 strftime、abs）会使查询中的任何索引无法使用。 - ypercubeᵀᴹ

我必须写上“order by ClosestDate”才能让它对我起作用；否则非常好，谢谢。 - minisaurus

Answer 2

如果表格很大，并且datetime列上有索引，那么以下方法将使用索引获取最接近提供值的2行（上下行），并且效率更高：

select * 
from
  ( select * 
    from 
    ( select t.ID, t.Price, t.PriceDate
      from Test t
      where t.PriceDate <= datetime('2015-06-23 10:00:00') 
      order by t.PriceDate desc
      limit 1
    ) d
  union all
    select * from
    ( select t.ID, t.Price, t.PriceDate
      from Test t
      where t.PriceDate > datetime('2015-06-23 10:00:00') 
      order by t.PriceDate asc
      limit 1
    ) a
  ) x
order by abs(julianday('2015-06-23 10:00:00') - julianday(PriceDate))
limit 1 ;

在SQLfiddle中进行了测试。

- ypercubeᵀᴹ

---

我用500张每张表中大约有150行（实际上是使用时间列而不是datetime列）的数据测试了您的解决方案。对我来说，@BerndLinde的解决方案要快大约40％。我测试了各种索引：在列上、在时间（列）上、在strftime（'%s'，列）上，但没有任何性能改进。 - ALalavi

150行是如此微小的表格，那么差异有多大？这两个查询是否都在毫秒级别运行？ - ypercubeᵀᴹ

Answer 3

另一个有用的解决方案是使用BETWEEN运算符，如果您可以确定时间/日期查询的上限和下限。我最近在这个链接中遇到了这个解决方案。这是我在名为t的时间列上用于我的应用程序的代码（更改日期列和日期函数的代码并不困难）：

select *
from myTable 
where t BETWEEN '09:35:00' and '09:45:00' 
order by ABS(strftime('%s',t) -  strftime('%s','09:40:00')) asc 
limit 1

另外，我必须更正我在上面帖子中的评论。我尝试了@BerndLinde、@ypercubeᵀᴹ和我的三种方法的速度进行简单测试。我有大约500个表，每个表有150行，并且在我的PC上使用中等硬件。结果如下：

1. 方案1（使用strftime）大约需要12秒。
2. 将列t的索引添加到方案1中可以提高大约30％的速度，大约需要8秒钟。对于使用时间（t）的索引，我没有看到任何改进。
3. 方案2的速度也比方案1提高了约30％，大约需要8秒钟
4. 最后，方案3的速度提高了约50％，大约需要5.5秒钟。添加列t的索引可以再次略微提高速度，大约需要4.8秒钟。在这个解决方案中，时间（t）的索引没有影响。

注意：我只是一名简单的程序员，这只是.NET代码中的一个简单测试。真正的性能测试必须考虑更多专业方面，而我不知道这些方面。在从数据库查询和读取之后，我的代码中还进行了一些计算。此外，正如@ ypercubeᵀᴹ所述，这个结果可能不适用于大量数据。

- ALalavi