서로 다른 두 날짜 범위를 비교하는 동안 TIMEDIFF의 합계

서로 다른 두 날짜 범위를 비교하는 동안 TIMEDIFF의 합계

SQL 테이블이 두 개 있습니다.하나는 작업 이름을 포함합니다.두 번째 테이블에는 작업에 태그가 지정된 날짜/시간 항목(수신 및 발신)이 있습니다.

작업 테이블

jobId,  jobName
1       MyJob
2       AnotherJob
3       ThisJob

타이머 테이블

timerId,  timerIn,              timerOut,             jobs_jobId
1         2020-07-30 16:03:00   2020-07-30 16:17:11   1
2         2020-07-30 14:00:00   2020-07-30 15:00:00   3
3         2020-06-30 16:17:12   2020-07-30 16:49:52   1
4         2020-07-30 16:20:00   2020-07-30 16:38:00   2
5         2020-06-24 08:44:00   2020-07-24 11:18:00   3
6         2020-06-30 15:35:00   2020-07-30 16:02:00   1

현재 쿼리는 현재 한 달 동안의 작업당 총 시간을 합산하여 완벽하게 작동합니다.

SELECT 
    `jobName`,
    SUM(TIME_TO_SEC(TIMEDIFF(`timerOut`, `timerIn`)) / 3600) AS `thismonthHours`
FROM
    `timers`
        INNER JOIN
    `jobs` ON `jobs`.`jobId` = `timers`.`jobs_jobId`
WHERE
    `timerIn` >= '2020-07-01 00:00:00'
        AND `timerOut` <= '2020-07-31 23:59:59'
GROUP BY `jobName`

출력(위에 표시된 더 큰 샘플 기준)

jobName,    thismonthHours
MyJob       81.3484
AnotherJob  9.6500
ThisJob     4.9500

단일 쿼리에서 이번 달과 이전 달의 시간 합계를 출력하는 방법을 찾고 있습니다. 출력은 다음과 같아야 합니다.

jobName,    thismonthHours, lastmonthHours
MyJob       81.3484         54.8150
AnotherJob  9.6500          68.1425
ThisJob     4.9500          1.2412

그것은 당신이 생각할 수 있는 것보다 더 복잡합니다.작업 실행이 두 달에 걸쳐 진행될 가능성을 염두에 두어야 합니다. 이 경우에는 실행 시간을 세분화해야 합니다.

왼쪽 조인을 사용한 접근 방식입니다.least()그리고.greatest()현재 월을 동적으로 계산하므로 월이 변경될 때 쿼리를 다시 작성할 필요가 없습니다.

select 
    j.jobName,
    sum(case when t.timerOut  >= d.dt
        then unix_timestamp(least(t.timerOut, d.dt + interval 1 month)) - unix_timestamp(greatest(t.timerIn, d.dt))
        else 0
    end) / 3600 hours_this_month,
    sum(case when t.timerIn  < d.dt
        then unix_timestamp(least(t.timerOut, d.dt)) - unix_timestamp(greatest(t.timerIn, d.dt - interval 1 month))
        else 0
    end) / 3600 hours_last_month
from (select date_format(current_date, '%Y-%m-01') dt) d
cross join jobs j
left join timers t
    on  j.jobId = t.jobs_jobId
    and t.timerIn  <  d.dt + interval 1 month
    and t.timerOut >= d.dt - interval 1 month
group by j.jobId, j.jobname
order by j.jobId

실제 표본 데이터에 대해 기대하는 결과가 표시되지 않았습니다.이 dbidle에서는 다음을 생성합니다.

jobName | hours_this_month | hours_last_month:--------- | ---------------: | ---------------:내 직업 | 1425.1008333333 | 16.13000000다른 직업 | 0.30000000 | 0.00000000
이 직업 | 564.30000000 | 159.26666667

언급URL : https://stackoverflow.com/questions/63193268/sum-of-timediff-while-comparing-two-different-date-ranges