MySQL select DATETIME similar up to the minute

Question

I have to comapre the results relative to the same time between two tables, but the time stamps differs of some second because of how they were recorded. I would like to obtain a result like in Example 1 but I get only the values with the asterisk, as in Example 2. What is the best way to remove the secods from the comparison, or to select the value corresponding to the closest DATETIME value?

Currently I'm using this query:

SELECT Table1.TimeSTamp1, Table1.Param1, Table2.TimeStamp2, Table2.Param2 
    FROM Table1, Table2 
    WHERE ... conditions for the other parameters of Table1 and Table2... 
    AND Table1.TimeSTamp1 = Table2.TimeStamp2

Any suggestion on the best practice is warmly welcomed.

Example 1

TimeStamp1          ¦   Param1  ¦   TimeStamp2          ¦   Param2
2011-01-01 00:00:35 ¦   1       ¦   2011-01-01 00:00:35 ¦   a       *
2011-01-01 00:01:35 ¦   2       ¦   2011-01-01 00:01:35 ¦   b
2011-01-01 00:02:37 ¦   3       ¦   2011-01-01 00:02:35 ¦   c
2011-01-01 00:03:31 ¦   4       ¦   2011-01-01 00:03:35 ¦   d
2011-01-01 00:04:32 ¦   5       ¦   2011-01-01 00:04:35 ¦   e
2011-01-01 00:05:38 ¦   6       ¦   2011-01-01 00:05:35 ¦   f
2011-01-01 00:06:36 ¦   7       ¦   2011-01-01 00:06:36 ¦   g       *
2011-01-01 00:07:32 ¦   8       ¦   2011-01-01 00:07:35 ¦   h
2011-01-01 00:08:33 ¦   9       ¦   2011-01-01 00:08:35 ¦   i
2011-01-01 00:09:33 ¦   10      ¦   2011-01-01 00:09:33 ¦   l       *
2011-01-01 00:10:35 ¦   11      ¦   2011-01-01 00:10:35 ¦   m       *
2011-01-01 00:11:29 ¦   12      ¦   2011-01-01 00:11:31 ¦   n

lll Example 2

TimeStamp1          ¦   Param1  ¦   TimeStamp2          ¦   Param2
2011-01-01 00:00:35 ¦   1       ¦   2011-01-01 00:00:35 ¦   a
2011-01-01 00:06:36 ¦   7       ¦   2011-01-01 00:06:36 ¦   g
2011-01-01 00:09:33 ¦   10      ¦   2011-01-01 00:09:33 ¦   l
2011-01-01 00:10:35 ¦   11      ¦   2011-01-01 00:10:35 ¦   m

O. Jones · Accepted Answer · 2019-05-15T17:57:24.683

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.

Thanks, TIMESTAMPDIFF makes the work. sometimes I get double entries and removing them takes a lot of time. I'll test if converting the date is faster in this case. — Andrew Strathclyde, Oct 28 '11 at 15:43

score 1 · Answer 2 · answered Oct 28 '11 at 11:33

1

WHERE ...
AND ABS(UNIX_TIMESTAMP(TimeStamp1) - UNIX_TIMESTAMP(TimeStamp2)) < :threshold:

where threshold is the number of seconds after which you no longer want a match (e.g. 60 for 1 minute).

answered Oct 28 '11 at 11:33

Hammerite

21,755
6
70
91

score 1 · Answer 3 · edited May 23 '17 at 12:08

You could use TIMESTAMPDIFF to compute the difference in datetimes in seconds:

SELECT t1.TimeStamp1, t1.Param1, t2.TimeStamp2, t2.Param2
FROM Table1 t1
LEFT JOIN Table2 t2
ON ABS(TIMESTAMPDIFF(SECOND,t1.TimeStamp1,t2.TimeStamp2))<=4
WHERE ...

Noting Ollie Jones warning, I've tested TIMESTAMPDIFF on MySQL version 5.1.58 and found no overflow with timestamps differing by up to at least 10000 years. So maybe this problem has been fixed.

MySQL select DATETIME similar up to the minute

3 Answers3

Linked