How to join on timestamp columns with tolerance

Question

How can I allow a tolerance of 1 - 10 sec?

I have 2 tables that have timestamps, yet are off by 1-10 seconds. I am using the inner join to compare them. I have phone numbers (columns) in both tables that match up but cant make them match up with the timestamps (time only). To allow a certain tolerance I could use an expression like cast(DateCreated as Time) = cast(TIMESTAMP as time) + 5. But I don't want to be doing this for every second.

SELECT Time_To_Sec(cast("table1_DateCreated" as time)) as DateCreated 
     , Time_To_Sec(cast("table2"."Timestamp" as time)) as Timestampe
     , "Direction","FromTW", "ToTW", "table2"."ANI","table2"."CALL ID"
     , "table2"."Disposition"
FROM "calls and time"
INNER JOIN "table2" on cast(DateCreated as Time)=cast(TIMESTAMP as time)+5
                   and FromTW="table2"."ANI"

I would like to see the following results if possible:

table1(DateCreated)  |  table2(Timestamp)  | compared results
---------------------+---------------------+-----------------
5000                 |  5005               |  table3
5001                 |  5009               |  table3
5001                 |  5050               |  not in table3   

If the condition is met then it will be sent to table 3 but if does not meet the condition the it will not be sent to table 3.

Using Zoho reports, therefore I'm not sure what type of database they are using. Postgres, MySQL, etc.

Answer 1

If timestamps in both tables can be off by 1-10 seconds, then it follows logically that the maximum tolerance for a match is 20 sceonds (+/- 10 on each side).

Tested in Postgres:

SELECT *
FROM   "calls and time" t1
JOIN   table2 t2 ON t2."TIMESTAMP" BETWEEN t1."DateCreated" - interval '20 sec'
                                       AND t1."DateCreated" + interval '20 sec';
WHERE  t1."FromTW" = t2."ANI"  -- FromTW or "FromTW" ?

This is a sargable expression that allows to use an index on x:

x BETWEEN y - interval '20 sec'
      AND y + interval '20 sec'

"calls and time", "TIMESTAMP", "FromTW" are extremely unfortunate identifiers, btw. Stick to legal, unquoted names to save some trouble.