How to get the closest dates in Oracle sql

Question

For example, I have 2 time tables: T1

id time 1 18:12:02 2 18:46:57 3 17:49:44 4 12:19:24 5 11:00:01 6 17:12:45 

and T2

 id time 1 18:13:02 2 17:46:57 

I need to get the time from T1, which are closest to the time from T2. There is no relationship between these tables. It should be something like this:

 select T1.calldatetime from T1, T2 where T1.calldatetime between T2.calldatetime-( select MIN(ABS(T2.calldatetime-T1.calldatetime)) from T2, T1) and T2.calldatetime+( select MIN(ABS(T2.calldatetime-T1.calldatetime)) from T2, T1) 

But I can’t understand. Any suggestions?

Answer 1

I believe this is the request you are looking for:

 CREATE TABLE t1(id INTEGER, time DATE); CREATE TABLE t2(id INTEGER, time DATE); INSERT INTO t1 VALUES (1, TO_DATE ('18:12:02', 'HH24:MI:SS')); INSERT INTO t1 VALUES (2, TO_DATE ('18:46:57', 'HH24:MI:SS')); INSERT INTO t1 VALUES (3, TO_DATE ('17:49:44', 'HH24:MI:SS')); INSERT INTO t1 VALUES (4, TO_DATE ('12:19:24', 'HH24:MI:SS')); INSERT INTO t1 VALUES (5, TO_DATE ('11:00:01', 'HH24:MI:SS')); INSERT INTO t1 VALUES (6, TO_DATE ('17:12:45', 'HH24:MI:SS')); INSERT INTO t2 VALUES (1, TO_DATE ('18:13:02', 'HH24:MI:SS')); INSERT INTO t2 VALUES (2, TO_DATE ('17:46:57', 'HH24:MI:SS')); SELECT t1.*, t2.* FROM t1, t2, ( SELECT t2.id, MIN (ABS (t2.time - t1.time)) diff FROM t1, t2 GROUP BY t2.id) b WHERE ABS (t2.time - t1.time) = b.diff; 

Make sure the time columns have the same date, because the t2.time - t1.time part will not work otherwise.

EDIT : Thanks for agreeing, but Ben's answer below is better. It uses Oracle analytic functions and will work much better.

Answer 2

You need to use only one Cartesian connection to solve your problem, unlike other solutions that use several. I assume the time is stored as VARCHAR2. If it is stored as a date, you can remove the TO_DATE functions. If it is stored as a date (I would highly recommend this), you will have to delete the parts of the date

I made it a little detailed, so obviously what is happening.

 select * from ( select id, tm , rank() over ( partition by t2id order by difference asc ) as rnk from ( select t1.*, t2.id as t2id , abs( to_date(t1.tm, 'hh24:mi:ss') - to_date(t2.tm, 'hh24:mi:ss')) as difference from t1 cross join t2 ) a ) where rnk = 1 

Basically, this expresses the absolute difference between each time in T1 and T2, and then selects the smallest difference on T2 ID ; returning data from T1.

Here it is in SQL Fiddle format.

Less nice (but shorter) format:

 select * from ( select t1.* , rank() over ( partition by t2.id order by abs(to_date(t1.tm, 'hh24:mi:ss') - to_date(t2.tm, 'hh24:mi:ss')) ) as rnk from t1 cross join t2 ) a where rnk = 1 

Answer 3

Here we select this line from T1, which has / has the smallest distance to any in T2:

 select T1.id, T1.calldatetime from T1, T2 where ABS(T2.calldatetime-T1.calldatetime) =( select MIN(ABS(T2.calldatetime-T1.calldatetime))from T1, T2); 

(tested it with mysql, hope you don't get ORA from this)

Edit : according to the last comment, it should be like this:

 drop table t1; drop table t2; create table t1(id int, t time); create table t2(id int, t time); insert into t1 values (1, '18:12:02'); insert into t1 values (2, '18:46:57'); insert into t1 values (3, '17:49:44'); insert into t1 values (4, '12:19:24'); insert into t1 values (5, '11:00:01'); insert into t1 values (6, '17:12:45'); insert into t2 values (1, '18:13:02'); insert into t2 values (2, '17:46:57'); select ot2.id, ot2.t, ot1.id, ot1.t from t2 ot2, t1 ot1 where ABS(ot2.t-ot1.t)= (select min(abs(t2.t-t1.t)) from t1, t2 where t2.id=ot2.id) 

It produces:

 id t id t 1 18:13:02 1 18:12:02 2 17:46:57 3 17:49:44 

Answer 4

Another way to use analytic functions. May be weird :)

 select id, time, case when to_date(time, 'hh24:mi:ss') - to_date(lag_time, 'hh24:mi:ss') < to_date(lead_time, 'hh24:mi:ss') - to_date(time, 'hh24:mi:ss') then lag_time else lead_time end closest_time from ( select id, tbl, LAG(time, 1, null) OVER (ORDER BY time) lag_time, time, LEAD(time, 1, null) OVER (ORDER BY time) lead_time from ( select id, time, 1 tbl from t1 union all select id, time, 2 tbl from t2 ) ) where tbl = 2 

In SQLFiddle ... and more!

Answer 5

Try this query a little long, I will try to optimize it

 select * from t1 where id in ( select id1 from (select id1,id2, rank() over (partition by id2 order by diff) rnk from (select distinct t1.id id1,t2.id id2, round(min(abs(to_date(t1.time,'HH24:MI:SS') - to_date(t2.time,'HH24:MI:SS'))),2) diff from t1,t2 group by t1.id,t2.id) ) where rnk = 1);