Closest Matching for MySQL datetime Fields

I've got raw data from two sensors in a database. In order to calculate meaningful results from these sensors, I need one datapoint from each. The catch is that their timestamps are not guaranteed to match up exactly. I could make a request for MySQL to take a timestamp from one sensor and find the timestamp that's closest for the other sensor, but this request would need to be run for each row, then again whenever a new row is added, and I'm concerned that searching through a table whenever a datapoint gets inserted might become a performance burden especially if more sensors like this are added. For a naive algori开发者_高级运维thm at least, finding the closest timestamp seems like a linear time operation. Could this cause a performance problem, or does MySQL have an optimization in this regard?

You could use a simple query to fetch the result closest to the given value:

SELECT `timestampcolumn`
FROM `thetable`
ORDER BY ABS(`timestampcolumn` - givenvalue)
LIMIT 1

Should do the trick and is not very performance intensive if you use a index on the right columns. MySQL is made for queries like this.

1. You can use MySQL functions to make a Mask to transform timestamp in japanese date like 20130109 (yyyymmddhhmmsszzz) or by code using PHP, java etc.;

2. At the time you try to find the rows with a SELECT you can do things like:

SELECT * FROM TABLE WHERE ADATE BETWEEN(LEFT(BEGINDATE, 8), LEFT(ENDDATE, 8));

OR

SELECT * FROM TABLE WHERE ADATA < LEFT(MYDATE, 8) LIMIT 1;

OR

USE A WILDCARD LIKE "_" IN THE PLACE YOU NEED, LIKE THE MONTH OU THE DAY YOU WISH TO VARIATE.