SQLite查询以获取最接近的日期时间 [英] SQLite query to get the closest datetime

问题描述

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

I am trying to write an SQLite statement to get the closest datetime from an user input (from a WPF datepicker). I have a table IRquote(rateId, quoteDateAndTime, quoteValue).

例如，如果用户输入10/01/2000并且数据库仅存储了针对08 / 01 / 2000、07 / 01/2000和14/01/2000，它将返回08/01/2000，即距10/01/2000最近的日期。

For example, if the user enter 10/01/2000 and the database have only fixing stored for 08/01/2000, 07/01/2000 and 14/01/2000, it would return 08/01/2000, being the closest date from 10/01/2000.

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

Of course, I'd like it to work not only with dates but also with time.

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

I tried with this query, but it returns the row with the furthest date, and not the closest one:

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

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

Note that I have a function DateTimeSQLite to transform user input to the right format.

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

I don't get why this does not work. How could I do it? Thanks for your help

推荐答案

要获取最接近的日期，您需要使用 strftime（'％s'，日期时间） SQLite函数。

To get the closest date, you will need to use the strftime('%s', datetime) SQLite function.

使用此示例/演示，您将获得与您最近的日期

请注意，日期 2015-06-25 10:00:00 是用户选择的输入日期时间。

With this example/demo, you will get the most closest date to your given date.
Note that the date 2015-06-25 10:00:00 is the input datetime that the user selected.

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 函数使其全部为正数，以确保我们的订单金额和随后的限制1 部分正常工作。

SQL explanation:
We use the strftime('%s') - strftime('%s') to calculate the difference, in seconds, between the two dates (Note: it has to be '%s', not '%S'). Since this can be either positive or negative, we also need to use the abs function to make it all positive to ensure that our order by and subsequent limit 1 sections work correct.

这篇关于SQLite查询以获取最接近的日期时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！