CODEWITHSUNDEEP
sqlsqlitedatetime
Sithered
Sithered

Reputation: 481

SQLite query to get the closest datetime

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).

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;

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

Upvotes: 3

Views: 3727

Answers (3)

ALalavi
ALalavi

Reputation: 115

Another useful solution is using BETWEEN operator, if you can determine upper and lower bounds for your time/date query. I encountered this solution just recently here in this link. This is what i've used for my application on a time column named t (changing code for date column and date function is not difficult):

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

Also, i must correct my comment on above post. I tried a simple examination of speed of these 3 approaches proposed by @BerndLinde, @ypercubeᵀᴹ and me . I have around 500 tables with 150 rows in each and medium hardware in my PC. The result is:

  1. Solution 1 (using strftime) takes around 12 seconds.
  2. Adding index of column t to solution 1 improves speed by around 30% and takes around 8 seconds. I didn't face any improvement for using index of time(t).
  3. Solution 2 also has around 30% of speed improvement over Solution 1 and takes around 8 seconds
  4. Finally, Solution 3 has around 50% improvement and takes around 5.5 seconds. Adding index of column t gives a little more improvement and takes around 4.8 seconds. Index of time(t) has no effect in this solution.

Note: I'm a simple programmer and this is a simple test in .NET code. A real performance test must consider more professional aspects, which i'm not aware of them. There was also some computations in my code, after querying and reading from database. Also, as @ypercubeᵀᴹ states, this result my not work for large amount of data.

Upvotes: 2

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115590

If the table is big, and there is an index on the datetime column, this will use the index to get the 2 closest rows (above and below the supplied value) and will be more efficient:

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 ;

Tested in SQLfiddle.

Upvotes: 3

Bernd Linde
Bernd Linde

Reputation: 2152

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

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 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.

Upvotes: 13

Related Questions