mysql - 我想在 SQL 查询中显示总工作时间和分钟

Question

我想在 SQL 查询中显示总工作时间和分钟

select 
  mom.machinery_operation_master_id,
  mom.assigned_code,
  mom.record_type,
  (case when mom.record_type=1 then time(mom.entered_time) end) power_on_time,
  (case when mom.record_type=2 then time(mom.entered_time) end) power_off_time,
  (case when mom.record_type=3 then time(mom.entered_time) end) breakdown_time,
  (case when mom.record_type=4 then time(mom.entered_time) end) repaired_time  
from machinery_operation_master mom 
where mom.location_id = '9' and 
      mom.created_at between '2019-09-17 08:00:00' and '2019-09-18 07:59:00'

假设第一行的开机时间:13:00:00，第二行的关机时间:14:30:00，那么我的预期输出应该是这样的。总小时数和分钟数:1:30:00

Answer 1

使用 mariadb:结果为 HH:mm

select concat((TIMESTAMPDIFF(second, t1.power_on_time, t1.power_off_time) 
            - TIMESTAMPDIFF(second, t1.breakdown_time, t1.repaired_time))/3600, ':',
                ((TIMESTAMPDIFF(MINUTE, t1.power_on_time, t1.power_off_time)
                    - TIMESTAMPDIFF(MINUTE, t1.breakdown_time, t1.repaired_time)) % 60), ':00') as working_time
from
        (select 
          mom.machinery_operation_master_id,
          mom.assigned_code,
          mom.record_type,
          (case when mom.record_type=1 then time(mom.entered_time) end) power_on_time,
          (case when mom.record_type=2 then time(mom.entered_time) end) power_off_time,
          (case when mom.record_type=3 then time(mom.entered_time) end) breakdown_time,
          (case when mom.record_type=4 then time(mom.entered_time) end) repaired_time,
        from machinery_operation_master mom 
        where mom.location_id = '9' and 
              mom.created_at between '2019-09-17 08:00:00' and '2019-09-18 07:59:00') as t1