SQL to find mill-second aprt records

Question

I need some help, I need to find records where some of them have same time-stamp or 500 mill-second time difference, here is the example data:

1. 2013-07-27 11:23:12.677
2. 2013-07-27 11:23:12.378
3. 2013-07-27 11:22:14.110
4. 2013-07-27 11:21:24.103
5. 2013-07-26 12:24:31.455
6. 2013-07-26 12:24:31.455
7. 2013-07-26 11:23:12.367
8. 2013-07-26 11:22:32.111

The rec #1 & #2 are 299 mill-seconds apart, and rec # 5 and #6 0 mill-seconds apart, when search into below data, I should get records # 1, #2 and # 5, #6 and so on...

Please any help or suitable SQL will be greatly help me.

Answer 1

SQL engines differ in their date time functions. For many, simply subtracting them produces a difference in days.

So, the following query produces what you want:

select t1.*
from t t1 join
     t t2
     on abs(t1.ts - t2.ts) < (500.0 / (24*60*60*1000));

Or, alternatively:

select t.*
from t
where exists (select 1 from t t2 where abs(t.ts - t2.ts) < (500.0 / (24*60*60*1000)) )