在MySQL查询中查找最接近指定日期时间的日期时间

Question

我正在尝试在MySQL数据库中查找最接近我指定的日期时间值的日期时间，但我遇到了一些问题。

以下伪代码是我想要实现的：

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

- Jack Hayfield

Answer 1

关键思想是使用order by和limit：

如果你想要前面最接近的一个：

SELECT one
FROM table
WHERE datetimefield <= '2014-12-10 09:45:00'
ORDER BY datetimefield DESC
LIMIT 1;

如果您想获取最接近的时间戳，无论是往前还是往后，那么请使用TIMESTAMPDIFF()函数：

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

- Gordon Linoff

---

谢谢，我已经用一个更新的查询更新了OP，但是如果你能够帮忙解决一下性能问题，那就太好了，谢谢。 - Jack Hayfield

@JackHayfield . . . 那个编辑过的部分应该成为另一个问题，而不是这个问题的附录。 - Gordon Linoff

Answer 2

使用abs()函数会导致无法使用datetimefield索引。我建议先筛选出最接近的之前的日期和最接近的之后的日期，都使用索引，在之后再选择其中较接近的一个：

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

- Julian Ladisch

---

如果更复杂的话能够带来更好的性能，那并不见得是一件坏事，这也正是他在文章中想要表达的观点。 - thaspius

Answer 3

使用ABS()函数

SELECT one FROM table 
ORDER BY ABS(`datetimefield` - '2014-12-10 09:45:00') LIMIT 1

这将返回差异最小的行，即最接近的行。

- Pupil