Join between tables in 2 different databases with slight differences in timestamp (MySQL)

Question

As suggested from MySQL -- join between tables in 2 different databases?, I used the following format to retrieve data that have shared column values across the two tables from different mysql databases.

SELECT <...>
FROM A.table1 t1 JOIN B.table2 t2 ON t2.column2 = t1.column1;

I want to get the shared values from the tables for the same date, hour and minute. However, I realised that because the timestamp values in the timestamp column of my two tables, from different databases, are slightly different in terms of the seconds recorded 2018-01-21 23:30:05 vs 2018-01-21 23:30:15, I am thus unable to retrieve any common data. How can I modify my query such that I can query for common data between two tables from two different databases with regards to the timestamp values, while neglecting the 'seconds' part of the timestamp column?

What about `2018-01-21 23:29:59` and `2018-01-21 23:30:00`? The difference is only one second. — Paul Spiegel, Jan 21 '18 at 14:11
Is it possible that one of the columns you are attempted to join on is actually related to the timestamp? — Gordon Linoff, Jan 21 '18 at 14:12
@Paul: Thats a good thought but I've program my python scripts to record the values in the SQL database at 0 or 30 mins, so its unlikely to run into having slightly earlier times. The slight delay in seconds I get is probably because my scripts are using those seconds to receive data from the sensors. — Craver2000, Jan 21 '18 at 14:13

O. Jones · Accepted Answer · 2018-01-22T17:53:29.947

1

You can use

 ON t2.datestamp BETWEEN t1.datestamp - INTERVAL 2 SECOND
                     AND t1.datestamp + INTERVAK 2 SECOND

for the ON clause in your JOIN operation. That will allow a 4-second window for the match.

Of course if you set the window too wide you'll some matches you don't want, and if you set it too narrow you'll miss some.

edited Jan 22 '18 at 17:53

answered Jan 21 '18 at 14:11

O. Jones

103,626
17
118
172

Thank you for your help and mentioning the caveat of this method. I've increased the interval to 10 seconds, and retrieved some 'shared' rows. I will look through the tables again however, just in case there might be instances where the data were recorded more than 10 secs apart and I might miss them as suggested. – Craver2000 Jan 21 '18 at 14:26
@Craver2000 - And the wider you make the interval, the more likely you are to match up rows that should not be matched up. GIGO. – Rick James Jan 22 '18 at 12:34

score 1 · Answer 2 · answered Jan 21 '18 at 14:42

While I think O.Jones' answer is the better way to solve your issue, if you would really need to compare two timestamps "neglecting the 'seconds' part" you could do the following:

SELECT <...>
FROM A.table1 t1
JOIN B.table2 t2
  ON t2.column2 BETWEEN date_format(t1.column1, '%Y-%m-%d %H:%i:00')
                    AND date_format(t1.column1, '%Y-%m-%d %H:%i:59')

Or more accurate:

  ON  t2.column2 >= date_format(t1.column1, '%Y-%m-%d %H:%i:00')
  AND t2.column2 <  date_format(t1.column1, '%Y-%m-%d %H:%i:00') + interval 1 minute

or

  ON  t2.column2 >= t1.column1 - interval second(t1.column1) second
  AND t2.column2 <  t1.column1 - interval second(t1.column1) second + interval 1 minute

Join between tables in 2 different databases with slight differences in timestamp (MySQL)

2 Answers2