I am trying to find a datetime value in a mysql database that is the closest match to a datetime that i specify, i am having some trouble.

The following pseudo code is what i want to achieve:

SELECT one FROM table WHERE datetimefield is closest to "2014-12-10 09:45:00" LIMIT 1

Thanks in advance

EDIT

Thanks for the replies thus far, as it turns out the query is a little more complicated than i first thought to achieve the desired result, the ABS method works great with a few additions.

Next question is, is there anyway to reduce the performance requirements on the following query?

SELECT DISTINCT timegenerated, *other values*

FROM table1 e INNER JOIN table2 dt

ON e.circuit = dt.circuit

WHERE dt.circuit IN ("2", "3", "4", "5", "6", "7", "8")

AND e.circuit != 1

AND dt.siteid = 435

ORDER BY ABS(TIMESTAMPDIFF(MINUTE, timegenerated, "2014-12-09 14:15:00")) LIMIT 7

Or incorprate it to match multiple dates as i will need to be able to match multiple specific datetimes (maybe as much as 90 days) It is currently being run for each individual datetime from another array but i realise this isnt optimal. The query is being built dynamically in PHP.

The query time is currently around 1.1s per query so running this 30 times or more becomes problematic, the table has hundreds of thousands of rows.

Many thanks again!

解决方案

The key idea is to use order by and limit:

If you want the closest one before:

SELECT one

FROM table

WHERE datetimefield <= '2014-12-10 09:45:00'

ORDER BY datetimefield DESC

LIMIT 1;

If you want the closest, in either direction, then use TIMESTAMPDIFF():

ORDER BY abs(TIMESTAMPDIFF(second, datetimefield, '2014-12-10 09:45:00'))

LIMIT 1