MySQL选择DATETIME类似，一分钟 [英] MySQL select DATETIME similar up to the minute
问题描述
我必须将结果相对于两个表之间的时间相结合，但是由于记录的方式，时间戳有所不同。
我想获得一个结果，如示例1 ，但是我只得到带有星号的值，如示例2 中所示。
从比较中删除secods的最佳方式是什么，或选择与最近的DATETIME值对应的值？
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?
目前我在使用查询：
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.
示例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
推荐答案
这个MySql表达式将给您返回DATETIME值，秒钟清零。
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)
看看这个。 http://dev.mysql.com/doc/ refman / 5.5 / en / date-and-time-functions.html＃function_date-format 。所以你可能会得到这样一个查询：
Take a look at this. http://dev.mysql.com/doc/refman/5.5/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.
您可以使用TIMESTAMPDIFF（http://dev.mysql.com/doc/refman/5.5/en/date-and-time-function）从另一个时间戳中减去一个时间戳.html＃function_timestampdiff）。不过要小心。此功能仅在彼此之间几天内的时间戳的秒级别正常工作;它无限地溢出（正如我发现有很大的痛苦）。
You can subtract one timestamp from another with TIMESTAMPDIFF (http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_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.
这篇关于MySQL选择DATETIME类似，一分钟的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
