如何确定数据库列中的值之间的差值并返回最小差值？

Question

我在MySQL中有一列DATETIME值，我希望返回列中任何两个值之间的最小差值；我不需要知道差值是什么值，或者何时是差值，只需要知道这两个值之间的差值。

我的表看起来像这样。

id    | launch_date_time
----------------------------
1     | 2012-01-02 18:42:00
2     | 2012-04-05 07:23:50
 ...
x     | 2014-08-07 22:19:11

有人能为我指出构建这样一个查询的正确方向吗？

Answer 1

我的第一个想法是，如果您的表名为table，请执行以下操作

select min( abs( datediff(t1.launch_date_time, t2.launch_date_time) ) )
  from table t1
     , table t2
 where t1.id <> t2.id

这取决于这些表的大小，上面是O(N^2)个解决方案，在O(N * log )中，你可以通过排序来实现，结果是连续元素的最小值

// pseudo code
list = fromDb();
sort(list);
min = list[1] - list[0];
for i in 2 to list.size()
    min = min( min, list[i] - list[i-1] )

Answer 2

select id,FROM_UNIXTIME(launch_date_time) as uTime,max(FROM_UNIXTIME(launch_date_time)) as max from table;

 //do db stuff
  //set the lowest to highest possible number so first comparison it will become the lowest.

   $lowest = $result['max'];

// iterate through array to get each time
foreach ($result['uTime'] as $v)
{

 //iterate again to compare each time u got from first loop to each in this loop

   foreach ($result['uTime as $x)
   { 
 //subtract your first value from the current
        $diff = $v - $x;

 //if its lower then any previous, and its positive set the new lowest.
        if ($diff < $lowest and $diff > 0)
        {
           $lowest = $diff;
        }
   }
}
 echo $lowest;

还没有测试过..。

Answer 3

尝试这个查询-

SELECT
  t1.launch_date_time, MIN(t2.launch_date_time) launch_date_time2
FROM launch t1
  JOIN launch t2
    ON t2.launch_date_time > t1.launch_date_time
GROUP BY
  t1.launch_date_time
ORDER BY
  DATE(MIN(t2.launch_date_time)) * 86400 + TIME_TO_SEC(TIME(MIN(t2.launch_date_time))) - DATE(t1.launch_date_time) * 86400 + TIME_TO_SEC(TIME(t1.launch_date_time))
LIMIT 1