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 time from T1 that are the closest to time from T2. There is no relationship between this 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 get it. Any suggestions?

Answer 1

I believe this is the query 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 that the time columns have the same date part, because the t2.time - t1.time part won't work otherwise.

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

Answer 2

You only have to use a single Cartesian join to solve you problem unlike the other solutions, which use multiple. I assume time is stored as a VARCHAR2. If it is stored as a date then you can remove the TO_DATE functions. If it is stored as a date (I would highly recommend this), you will have to remove the date portions

I've made it slightly verbose so it's obvious what's going on.

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 works out the absolute difference between every time in T1 and T2 then picks the smallest difference by T2 ID; returning the data from T1.

Here it is in SQL Fiddle format.

The less pretty (but shorter) format is:

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