CODEWITHSUNDEEP
mysqlsqldatetimejoinmariadb
Craver2000
Craver2000

Reputation: 453

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

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?

Upvotes: 1

Views: 239

Answers (2)

O. Jones
O. Jones

Reputation: 108816

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.

Upvotes: 1

Paul Spiegel
Paul Spiegel

Reputation: 31812

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

Upvotes: 1

Related Questions