mysql - 使用内部联接来联接时间戳略有不同的 mySQL 列

Question

我想从两个数据库的不同表中进行查询，以时间戳为共同特征。目前，我的结果是空集，并带有多个警告。我的查询设计是:

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

表 A 来自数据库 A。表 B、C、D 来自数据库 B。ts 指时间戳，我根据公共(public)时间戳在表之间连接。然而，由于时间戳之间略有偏差几秒钟，我添加了例如INNER JOIN 之间的BETWEEN tableB.ts - INTERVAL 50 SECOND AND tableB.ts + INTERVAL 50 SECOND。我认为这可能是导致问题的原因，但是，我不太确定。如果是这样，我该如何修改这个问题？

Answer 1

更好的方法是使用内置函数，例如 ABS 和 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';