I would like to query from different tables from two databases, with timestamp as the common feature. Currently, I get an empty set for my result, with multiple warnings. My query design is:
SELECT tableA.ts, tableB.column1, time_to_sec(tableC.column1) as duration,
tableD.column1 FROM databaseA.tableA \
INNER JOIN databaseB.tableB ON tableA.ts = tableB.ts \
BETWEEN tableA.ts - INTERVAL 50 SECOND AND tableA.ts + INTERVAL 50 SECOND \
INNER JOIN databaseB.tableC ON tableB.ts = tableC.ts \
BETWEEN tableB.ts - INTERVAL 50 SECOND AND tableB.ts + INTERVAL 50 SECOND \
INNER JOIN databaseB.tableD ON tableC.ts = tableD.ts \
BETWEEN tableC.ts - INTERVAL 50 SECOND AND tableC.ts + INTERVAL 50 SECOND \
WHERE TIME(tableA.ts) between '08:59:00' AND '09:01:00'; #getting values at 9am here
Table A is from database A. Tables B, C, D are from database B. ts refers to timestamp, which I am joining between tables based on common timestamps. However, since the timestamps are slightly off between each other by a couple of seconds, I added e.g. BETWEEN tableB.ts - INTERVAL 50 SECOND AND tableB.ts + INTERVAL 50 SECOND between the INNER JOIN. I think this might be causing the problem, however, I am not too sure. If so, how can I amend the issue?
A better way is to use built-in functions, like ABS and TIMESTAMPDIFF:
SELECT tableA.ts, tableB.column1, TIME_TO_SEC(tableC.column1) as duration, tableD.column1
FROM databaseA.tableA
INNER JOIN databaseB.tableB ON ABS(TIMESTAMPDIFF(SECOND, tableA.ts, tableB.ts)) <= 50
INNER JOIN databaseB.tableC ON ABS(TIMESTAMPDIFF(SECOND, tableB.ts, tableC.ts)) <= 50
INNER JOIN databaseB.tableD ON ABS(TIMESTAMPDIFF(SECOND, tableC.ts, tableD.ts)) <= 50
WHERE TIME(tableA.ts) between '08:59:00' AND '09:01:00';