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

Question

我正试图编写一条SQLite语句，以从用户输入（从WPF日期选择器）中获取最接近的日期时间。我有一个表IRquote（rateId，quoteDateAndTime，quoteValue）。

例如，如果用户输入10/01/2000，并且数据库只存储了2000年1月8日、2000年1日7日和2000年1年1月14日的修复，则它将返回2000年01月8日，这是自2000年10月1日起最近的日期。

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

我尝试过这个查询，但它返回了日期最远的一行，而不是最近的一行：

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

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

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

Answer 1

要获得最近的日期，您需要使用strftime（'%s'，datetime）SQLite函数。

通过这个示例/演示，您将获得最接近给定日期的日期
注意，日期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部分正确工作。

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中测试。

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行，我的电脑里有中等硬件。结果是：

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ᵀᴹ州，这一结果我不适用于大量的数据。