MySQL select DATETIME similar up to the minute
This MySql expression will give you back DATETIME values with the seconds zeroed out.
CONVERT(DATE_FORMAT(table.column,'%Y-%m-%d-%H:%i:00'),DATETIME)
Take a look at this. https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_date-format . So you might end up with a query like this:
SELECT Table1.TimeSTamp1, Table1.Param1, Table2.TimeStamp2, Table2.Param2
FROM Table1
JOIN Table2 ON CONVERT(DATE_FORMAT(Table1.TimeStamp1,'%Y-%m-%d-%H:%i:00'),DATETIME)
= CONVERT(DATE_FORMAT(Table2.TimeStamp2,'%Y-%m-%d-%H:%i:00'),DATETIME)
WHERE ... conditions for the other parameters of Table1 and Table2...
But, be careful. Autogenerated timestamps are kind of like floating point numbers; when two of them turn up equal to each other it's just luck. Truncating your timestamps to the minute may be OK, but you may also be better off subtracting one timestamp from another, and comparing the differences (or the absolute values of the differences).
Also, this join is going to be slow because it has to run the second-truncating function on every value, so it can't use any indexes.
You can subtract one timestamp from another with TIMESTAMPDIFF()
. But be careful. This function only works correctly at the level of seconds for timestamps within a few days of each other; it overflows gracelessly (as I discovered with great pain).
You could try truncating the timestamps to minutes at the time you insert them. That would let you index them.