CODEWITHSUNDEEP
sqlitedatetime
Danuc
Danuc

Reputation: 83

How to get nearest DateTime from 2 tables


In SQLite, I want to build a query to get the nearest datetime for 'tag' entries against a 'tick' list:

CREATE TABLE Tick    (
     id integer primary key, 
     dt varchar(20) 
    );

INSERT INTO Tick (id, dt) VALUES 
( 1, '2018-10-30 13:00:00'), 
( 2, '2018-10-30 14:00:00'), 
( 3, '2018-10-30 15:00:00'), 
( 4, '2018-10-30 16:00:00'), 
( 5, '2018-10-30 17:00:00'), 
( 6, '2018-10-30 18:00:00'), 
( 7, '2018-10-30 19:00:00'), 
( 8, '2018-10-31 05:00:00'), 
( 9, '2018-10-31 06:00:00'), 
(10, '2018-10-31 07:00:00');


CREATE TABLE Tag    (
     id integer primary key, 
     dt varchar(20) 
    );

INSERT INTO Tag (id, dt) VALUES 
(100, '2018-10-30 16:08:00'), 
(101, '2018-10-30 17:30:00'), 
(102, '2018-10-30 19:12:00'), 
(103, '2018-10-31 04:00:00'), 
(104, '2018-10-31 13:00:00');

The following query gives me the good match (based on diff) but I'm unable to get Tick columns:

SELECT Tag.dt,
       (SELECT ABS(strftime('%s',Tick.dt) - strftime('%s',Tag.dt)) as diff
        FROM Tick
        ORDER BY diff ASC
        LIMIT 1
       ) as diff from Tag


I tried the following but I receive an error on Tag.dt in ORDER BY:

SELECT 
     Tag.id, Tag.dt,
     Tick.id, Tick.dt,
     abs(strftime('%s',Tick.dt) - strftime('%s',Tag.dt)) as Diff FROM Tag   JOIN Tick   ON Tick.dt =    (SELECT Tick.dt
    FROM Tick 
    ORDER BY abs(strftime('%s',Tick.dt) - strftime('%s',Tag.dt)) ASC
    limit 1)


The result I would like to have is something like:

TagID,DateTimeTag        ,TickID,DateTimeTick
    100,2018-10-30 16:08:00,     4,2018-10-30 16:00:00
    101,2018-10-30 17:30:00,     6,2018-10-30 18:00:00
    102,2018-10-30 19:12:00,     7,2018-10-30 19:00:00
    103,2018-10-31 04:00:00,     8,2018-10-31 05:00:00
    104,2018-10-31 13:00:00,    10,2018-10-31 07:00:00

Edited later...
Based on forpas's answer, I was able to derive something without using the ROW_COUNTER() keyword which I can't use in FME. I also set a maximum delta time difference (10000 sec) to find a match:

SELECT t.TagId, t.Tagdt, t.TickId, t.Tickdt, MIN(t.Diff)
FROM
(
SELECT 
     Tag.id as TagId, Tag.dt as Tagdt,
     Tick.id as TickId, Tick.dt as Tickdt,
     abs(strftime('%s',Tick.dt) - strftime('%s',Tag.dt)) as Diff
FROM Tag, Tick
WHERE Diff < 10000
) AS t
GROUP BY t.TagId


Thanks again!

Upvotes: 1

Views: 75

Answers (2)

Kalyan Raghu
Kalyan Raghu

Reputation: 1035

Create a temp_table query to get the differences of time stamps of the cross product of Tick and Tag tables and select the min value for each of the Tick table id s. The two temp_table queries are identical. Note that this query may not be efficient as it takes full cross product across the two tables

SELECT temp_table.tid, temp_table.tdt, temp_table.tiid, temp_table.tidt, temp_table.diff
FROM
(SELECT Tag.id AS tid, Tag.dt AS tdt, Tick.id AS tiid, Tick.dt AS tidt, abs(strftime('%s',Tick.dt) - strftime('%s',Tag.dt)) as diff
FROM tag, tick) temp_table

WHERE temp_table.diff =

(SELECT MIN(temp_table2.diff) FROM
    (SELECT Tag.id AS tid, Tag.dt AS tdt, Tick.id AS tiid, Tick.dt AS tidt, abs(strftime('%s',Tick.dt) - strftime('%s',Tag.dt)) as diff
    FROM tag, tick) temp_table2
    WHERE temp_table2.tid = temp_table.tid
)

group by temp_table.tid

Upvotes: 0

forpas
forpas

Reputation: 164139

Use ROW_NUMBER() window function:

SELECT t.tagID, t.tagDT, t.tickID, t.tickDT
FROM (
  SELECT t.*, 
    ROW_NUMBER() OVER (PARTITION BY t.tagID, t.tagDT ORDER BY t.Diff) AS rn
  FROM (
    SELECT Tag.id tagID, Tag.dt tagDT, Tick.id tickID, Tick.dt tickDT,
      ABS(strftime('%s',Tick.dt) - strftime('%s',Tag.dt)) as Diff 
    FROM Tag CROSS JOIN Tick 
  ) AS t                                        
) AS t  
WHERE t.rn = 1

See the demo.
Rsults:

| tagID | tagDT               | tickID | tickDT              |
| ----- | ------------------- | ------ | ------------------- |
| 100   | 2018-10-30 16:08:00 | 4      | 2018-10-30 16:00:00 |
| 101   | 2018-10-30 17:30:00 | 5      | 2018-10-30 17:00:00 |
| 102   | 2018-10-30 19:12:00 | 7      | 2018-10-30 19:00:00 |
| 103   | 2018-10-31 04:00:00 | 8      | 2018-10-31 05:00:00 |
| 104   | 2018-10-31 13:00:00 | 10     | 2018-10-31 07:00:00 |

Upvotes: 1

Related Questions