SQL-Time difference

Question

2 Tables with the below model: Id: long unique; timestamp:long; price:double

The data in both tables are identical except timestamp. Timestamp is given as unix_time in ms.

Quest: how many pairs have a bigger time difference than 1000ms ?

Answer 1

Try this:

SELECT COUNT(*)
FROM Table1 t1 INNER JOIN Table2 t2 ON t1.id = t2.id
WHERE 
    (t1.timestamp - t2.timestamp > 1000) or 
    (t2.timestamp - t1.timestamp > 1000)

Answer 2

assuming the IDs are same for both tables and timestamp is the only different field

assume these are your tables

ID | timestamp | price

we will call them table1 and table2

select count(*) from table1 t1
 inner join table2 t2 on t1.ID=t2.ID
 where (t1.timestamp-t2.timestamp) > 1000
 or (t2.timestamp-t1.timestamp)>1000