How to Join Tables on Datetime within a margin of seconds

I have 10 tables: Table1, Table2, …, Table10

Each one with the columns:

• id
• text
• datetime

My task is to join the Table1.datetime with the rest of the 9 tables.datetime (+-3seconds on the 9 tables) and return the text columns of all tables. I am using postgresql.

Any ideas? Thanks in advance.

Answer

It’s not clear to me if the timestamp in table1 should be within +/- 3 seconds of the other tables or the other way round.

You can join on more complex conditions than just =:

select t1.text, t2.text, ...
from table1 t1
  join table2 t2 
    on t1.datetime >= t2.datetime - interval '3 seconds'
   and t1.datetime <= t2.datetime + interval '3 seconds'
  join table3 t3
    on t1.datetime >= t3.datetime - interval '3 seconds'
   and t1.datetime <= t3.datetime + interval '3 seconds'
  ... repeat for all tables ...

If you are not sure if all tables have a match, you might want a left join rather than an inner join.

The above assumes that the datetime columns are correctly defined with the data type timestamp or timestamptz