MySQL找到两个日期不同列之间的差异

Question

这是我的查询:

    select calldate as call_start, DATE_ADD(calldate, Interval duration SECOND) as call_end, duration
    from extensions 
    where ext = '2602' -- ext is the primary key
    AND DATE_FORMAT(calldate, '%Y-%m-%d') = "2015-03-20"
    order by calldate asc

返回以下内容:

我如何添加第 4 列来区分第二行的 call_start 和第一行的 call_end？像这样:

2015-03-20 10:21:20 - 2015-03-20 10:21:16 => 4 秒，这 4 秒应添加为第二行的第 4 个字段。

所以它应该看起来像所有调用:

call_start           call_end              duration       difference
2015-03-20 10:19:41  2015-03-20 10:21:16   95             null
2015-03-20 10:21:20  2015-03-20 10:21:29   9              4

由于第一行没有来自之前调用的 call_end，因此应该为空。

解决方案:

 SET @prev_end=null;
 select calldate, date_add(calldate, interval duration SECOND) as end_date, duration,
  TIME_FORMAT(SEC_TO_TIME(timestampdiff(second,@prev_end,calldate)), '%i:%s')as difference,
  @prev_end:= date_add(calldate, interval duration SECOND) as test
  from extensions
  where ext = '2602'
  AND DATE_FORMAT(calldate, '%Y-%m-%d') = "2015-03-20"
  order by calldate asc;

输出:

Answer 1

考虑下表

mysql> select * from extensions;
+---------------------+----------+
| calldate            | duration |
+---------------------+----------+
| 2015-03-20 10:19:41 |       95 |
| 2015-03-20 10:21:20 |        9 |
| 2015-03-20 10:21:35 |      277 |
| 2015-03-20 10:55:49 |       27 |
+---------------------+----------+

现在你可以得到不同的

select
calldate as start_date,
end_date,
duration,
difference
from(
  select 
  calldate, 
  duration,
  date_add(calldate, interval duration SECOND) as end_date , 
  timestampdiff(second,@prev_end,calldate) as difference,
  @prev_end:= date_add(calldate, interval duration SECOND)
  from extensions,(select @prev_end:=null)x 
  order by calldate
)x

+---------------------+---------------------+----------+------------+
| start_date          | end_date            | duration | difference |
+---------------------+---------------------+----------+------------+
| 2015-03-20 10:19:41 | 2015-03-20 10:21:16 |       95 |       NULL |
| 2015-03-20 10:21:20 | 2015-03-20 10:21:29 |        9 |          4 |
| 2015-03-20 10:21:35 | 2015-03-20 10:26:12 |      277 |          6 |
| 2015-03-20 10:55:49 | 2015-03-20 10:56:16 |       27 |       1777 |
+---------------------+---------------------+----------+------------+

在上面的查询中，您需要在子查询中的 where 子句之前添加额外的 where 子句

where ext = '2602' -- ext is the primary key
AND DATE_FORMAT(calldate, '%Y-%m-%d') = "2015-03-20"