如何在Mysql中将持续时间行分割为一小时段

Question

我有开始时间。从那里，我使用 Lead 函数来获取结束时间并计算持续时间。现在我试图按小时分割持续时间。这是当前的代码和输出：

SELECT `time`, LEAD(time,1,null) OVER (ORDER BY time asc) time_end, 
unix_timestamp(LEAD(time,1,null) OVER ( ORDER BY time asc)) - unix_timestamp(time) as 
duration
FROM test_table

当前输出：

+------+---------------------+---------------------+---------------+
| name |        start        |         end         | (duration)s   |
+------+---------------------+---------------------+---------------+
|  me  | 2017-04-03 23:00:00 | 2017-04-03 23:30:00 |          1800 |
|  me  | 2017-04-03 23:30:00 | 2017-04-04 00:45:00 |          5100 |
|  me  | 2017-04-04 10:00:00 | 2017-04-04 11:00:00 |          3600 |
|  me  | 2017-04-04 11:00:00 | 2017-04-04 11:30:00 |          1800 |
+------+---------------------+---------------------+---------------+

预期输出：

 +------+---------------------+---------------------+---------------+
 | name |        start        |         end         | (duration)s   |
 +------+---------------------+---------------------+---------------+
 |  me  | 2017-04-03 23:00:00 | 2017-04-03 23:30:00 |          1800 |
 |  me  | 2017-04-03 23:30:00 | 2017-04-04 00:00:00 |          1800 |
 |  me  | 2017-04-03 00:00:00 | 2017-04-04 00:45:00 |          2700 |
 |  me  | 2017-04-04 10:00:00 | 2017-04-04 11:00:00 |          3600 |
 |  me  | 2017-04-04 11:00:00 | 2017-04-04 11:30:00 |          1800 |
 +------+---------------------+---------------------+---------------+