mysql-按15分钟的间隔对时间范围进行分组

2ekbmq32 于 2021-06-21 发布在 Mysql

关注(0)|答案(2)|浏览(693)

所以，我有一个计时表，它跟踪我的员工花了多少时间登录到不同的州。看起来是这样的：

Employee_ID     Queue             StartTime            End Time
   121509      'Break'      2018-05-17 13:31:54  2018-05-17 13:47:02
   121509      'Working'    2018-05-17 13:47:04  2018-05-17 15:05:45
   121509      'Unavail.'   2018-05-17 15:05:46  2018-05-17 15:32:01

等等。我的目标是聚合所有员工在每个队列状态下花费的时间量（从endtime减去starttime），并按15分钟的间隔对聚合进行分组。我当时做的是：

concat(
    time_format(convert_tz(StartTime,'UTC','America/Denver'),'%H'),
    ':',
    case
        when minute(convert_tz(StartTime,'UTC','America/Denver')) < 15
            then '00'
        when minute(convert_tz(StartTime,'UTC','America/Denver')) < 30
            then '15'
        when minute(convert_tz(StartTime,'UTC','America/Denver')) < 45
            then '30'
        else '45'
    end,
    ':00'
) as 'Interval',

然而，通过这种方式分组，我意识到如果我用这种方式分组，那么在队列中花费的任何时间都将只计算员工登录的第一个时间间隔，而不是在starttime和endtime之间的时间范围所包围的所有时间间隔中分割时间。
所以，我的问题是：如何对数据进行分组，以便如果给定的时间范围超过了它开始的15分钟间隔，那么它开始计算下一个15分钟间隔？
输出示例：

Employee_ID   Interval      Queue    QueueTime
   121509        13:30      'Break'   00:14:54
   121509        13:45      'Break'   00:02:02
   121509        13:45     'Working'  00:13:58
   121509        14:00     'Working'  00:15:00
   121509        14:15     'Working'  00:15:00
   121509        14:30     'Working'  00:02:58
   121509        14:30     'Unavail.' 00:08:13
   121509        14:30     'Break'    00:03:28

sql mysql time Intervals

来源：https://stackoverflow.com/questions/50400370/mysql-grouping-time-ranges-by-fifteen-minute-intervals

2条答案

按热度按时间

xv8emn3q1#

我猜您会希望得到时间戳的差异，然后按雇员和队列类型分组。这样的查询应该适合您：

SELECT 
    Employee_ID,
    Queue,
    SUM(TIMESTAMPDIFF(MINUTE,
        StartTime,
        EndTime)) AS Time
FROM
    EmployeeTable
GROUP BY Employee_ID , Queue;

赞(0）回复(0）举报 2021-06-21

wi3ka0sx2#

好吧。我花了一些时间，但在rtoyo的帮助下我发现了：
首先：创建一个15分钟间隔的列表：

select 
        addtime(time('00:00:00'),sec_to_time(900 * (a.a + b.a * 10))) as 'IntervalStart',
        addtime(time('00:14:59'),sec_to_time(900 * (a.a + b.a * 10))) as 'IntervalEnd'
    from (
        select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
        cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
    where
        addtime(time('00:00:00'),sec_to_time(900 * (a.a + b.a * 10))) between '06:00:00' and '18:45:00'

下一步：通过测试starttime和endtime字段之间的重叠来加入计时表：

left join (     select 
        addtime(time('00:00:00'),sec_to_time(900 * (a.a + b.a * 10))) as 'Interval Start',
        addtime(time('00:14:59'),sec_to_time(900 * (a.a + b.a * 10))) as 'Interval End'
    from (
        select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
        cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
    where
        addtime(time('00:00:00'),sec_to_time(900 * (a.a + b.a * 10))) between '06:00:00' and '18:45:00') as Intervals
on timekeepingtable.StartTime <= Intervals.IntervalStart
and timekeepingtable.EndTime >= Intervals.IntervalEnd

然后只需修改starttime和endtime字段，使其以间隔为界：

if(time(pgrp.StartTime) < Intervals.IntervalStart,Intervals.IntervalStart,time(pgrp.StartTime)) as 'Start Time',
    if(time(pgrp.EndTime) > Intervals.IntervalEnd, Intervals.IntervalEnd,time(pgrp.EndTime)) as 'End Time',

赞(0）回复(0）举报 2021-06-21

我来回答

mysql-按15分钟的间隔对时间范围进行分组

2条答案

相关问题

热门标签

最新问答