CODEWITHSUNDEEP
sqlsqlitedatetime
NWOWN
NWOWN

Reputation: 407

How get closest date time in database in sqlite

Database store time and value like that.

    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

I want to get the val and datetimes of the closest time on the hour.

Example datetimes=2018-09-04 11:02:15 is closest 11:00:00, so I get (2018-09-04 11:02:15, 0.24)

I know how get closest time.

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

but it return only one record.

I want to receive all the records that match the condition

That is probably the result I want in example data

    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

Is it possible to use SQL in SQLite? If so, how can I do it?

Or should I do it with external code?

Upvotes: 1

Views: 294

Answers (2)

Turophile
Turophile

Reputation: 3405

This is what I came up with:

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;

Note that the results:

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

Are different to yours, because it includes the rows which are considered the closest to the next hour.

Working in a fiddle here: http://sqlfiddle.com/#!5/11522/42/0

Upvotes: 2

theDbGuy
theDbGuy

Reputation: 931

select 
date_time,
case when substr(strftime('%H.%M',date_time),4,5) <='30' then strftime('%H',date_time)
else strftime('%H',date_time)+1
end closest_hour,
value
from sample;

I have set 30 as the floor and ceil value.change it as per your requirement sqlfiddle link :Example

Upvotes: 1

Related Questions