MySQL select DATETIME 类似到分钟

Question

我必须比较两个表之间相对于同一时间的结果，但时间戳因记录方式而有所不同。 我希望获得像示例 1 中那样的结果，但我只得到带星号的值，如示例 2 中那样。 从比较中删除秒或选择与最接近的 DATETIME 值相对应的值的最佳方法是什么？

目前我正在使用这个查询：

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

热烈欢迎任何有关最佳实践的建议。

---

示例 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 示例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

Answer 1

这个 MySql 表达式将为您返回 DATETIME 值，并将秒数清零。

CONVERT(DATE_FORMAT(table.column,'%Y-%m-%d-%H:%i:00'),DATETIME)

看看这个。 https://dev。 mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_date-format。所以你最终可能会得到这样的查询：

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... 

但是，要小心。自动生成的时间戳有点像浮点数；当其中两个出现相同的情况时，这只是运气。将时间戳截断为分钟可能没问题，但最好将一个时间戳减去另一个时间戳，然后比较差异（或差异的绝对值）。

此外，此连接会很慢，因为它必须对每个值运行第二次截断函数，因此它不能使用任何索引。

您可以使用 将一个时间戳与另一个时间戳相减TIMESTAMPDIFF()。但要小心。该函数仅在时间戳相隔几天之内的秒级上正确工作；它毫无优雅地溢出（正如我痛苦地发现的那样）。

您可以尝试在插入时间戳时将时间戳截断为分钟。这会让你索引它们。

Answer 2

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

其中threshold 是您不再需要匹配的秒数（例如 1 分钟 60 秒）。

Answer 3

您可以使用 TIMESTAMPDIFF计算日期时间的差异（以秒为单位）：

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 ...

注意 Ollie Jones 警告，我已经测试了 TIMESTAMPDIFF MySQL 版本 5.1.58 未发现时间戳相差至少 10000 年的溢出情况。所以也许这个问题已经解决了。