Find closest datetime to specified datetime in mysql query

Question

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

Answer 1

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

Answer 2

Using abs() prevents using a datetimefield index. I propose to have one select for the closest before and one select for the closest after, both using the index, and picking the closest of them afterwards:

create table `table` (datetimefield datetime key, one varchar(99));
insert into `table` values
  ('2014-06-01', 'a'), ('2014-12-01', 'b'),
  ('2015-01-01', 'c'), ('2015-02-01', 'd');

set @d = '2014-12-10 09:45:00';

select * from
(
  ( select *, TIMESTAMPDIFF(SECOND, @d, datetimefield) as diff
    from `table` where datetimefield >= @d
    order by datetimefield asc  limit 1
  )
  union
  ( select *, TIMESTAMPDIFF(SECOND, datetimefield, @d) as diff
    from `table` where datetimefield < @d
    order by datetimefield desc limit 1
  )
) x
order by diff
limit 1;

http://sqlfiddle.com/#!2/bddb4/1