mysql - 开始停止时差按刻钟分组

Question

我有一个名为time的mysql表(示例1)，每天包含多个时间戳，它们代表登录和注销时间。

第二个表称为time_intervals，它为我提供了示例 2 的输出。

我需要计算出该用户登录的持续时间(按 15 分钟片段分组)。

示例 3 显示了我想要的结果。

示例1(时间)

start_time      | end_time            | duration_s | user | 
----------------------------------------------------------------
2017-02-07 06:01:13 | 2017-02-07 07:03:19 | 3726       | x    |
2017-02-07 07:31:29 | 2017-02-07 08:00:00 | 3726       | x    |
2017...............   ...................   ....         .

示例 2(time_intervals)

interval_start  | interval_end        | 
--------------------------------------------
2017-02-07 06:00:00 | 2017-02-07 06:14:59 |
2017-02-07 06:15:00 | 2017-02-07 06:29:59 |
2017-02-07 06:30:00 | 2017-02-07 06:44:59 |
2017-02-07 06:45:00 | 2017-02-07 06:59:59 |
2017-02-07 07:00:00 | 2017-02-07 07:14:59 |
2017............... | 2017...............

示例 3(所需输出)

interval_start      | interval_start      | duration_s | user | 
----------------------------------------------------------------
2017-02-07 06:00:00 | 2017-02-07 06:14:59 | 826    | x    |
2017-02-07 06:15:00 | 2017-02-07 06:29:59 | 899    | x    |
2017-02-07 06:30:00 | 2017-02-07 06:44:59 | 899    | x    |
2017-02-07 06:45:00 | 2017-02-07 06:59:59 | 899    | x    |
2017-02-07 07:00:00 | 2017-02-07 07:14:59 | 199    | x    |
2017-02-07 07:15:00 | 2017-02-07 07:30:59 | 0      | x    |
2017-02-07 07:30:00 | 2017-02-07 07:44:59 | 810    | x    |

任何形式的帮助将不胜感激。

Answer 1

只要 time_intervals 保存要显示的所有 15 分钟间隔，类似以下内容应该可以工作:

SELECT ti.interval_start,
       ti.interval_end,      
       TIMESTAMPDIFF(SECOND, GREATEST(t.start_time, ti.interval_start), 
                             LEAST(t.end_time, ti.interval_end)) AS duration_s
FROM time_intervals AS ti
LEFT JOIN time AS t 
   ON t.start_time <= ti.interval_end AND t.end_time >= ti.interval_start