如何将开始/结束数据的总小时数/天数分组（每天多次输入，有些则是午夜过夜）？

I want to know how to get the exact hours between two dates in current date.

我想知道如何获得当前日期两个日期之间的确切时间。

my table;

id | start      | s_clock   | finish      | f_clock
---------------------------------------------------
1  | 2017-11-10 | 22:00     | 2017-11-11  | 03:00
2  | 2017-11-11 | 09:00     | 2017-11-11  | 10:00

Expected result:

day        | total_hours 
--------------------------
2017-11-10 | 02:00            -- sum of all hours spent on 2017-11-10
2017-11-11 | 04:00            -- sum of all hours spent on 2017-11-11

Thanks for your help.

谢谢你的帮助。

1 个解决方案

#1

You should avoid using reserved keywords as table or field names.

您应该避免将保留关键字用作表或字段名称。

start is one of them.

开始就是其中之一。

create table times ( startt date, s_time time, finish date, f_time time);

insert into times
values 
( "2017-11-10" , "22:00", "2017-11-11" , "03:00"),
( "2017-11-11" , "09:00", "2017-11-11" , "10:00");

select time(sum(delta_on_day)),on_day  -- comment line to see ungrouped results
from -- comment line to see ungrouped results
(   -- comment line to see ungrouped results
  (
    select * 
    ,  timediff(time("24:00"), s_time)  as delta_on_day       
    ,  startt as on_day  
    from times 
    where startt < finish
  )
  UNION ALL 
  (
    select * 
    ,  timediff(f_time, time("0:0"))  as delta_on_day       
    ,  finish as on_day  
    from times 
    where startt < finish
  )
  UNION ALL 
  (
    select * 
    ,  timediff(f_time, s_time)  as delta_on_day       
    ,  startt as on_day  
    from times 
    where startt = finish
  )
) as tbl -- comment line to see ungrouped results
group by on_day -- comment line to see ungrouped results

see fiddle: http://sqlfiddle.com/#!9/189e21f/38

看小提琴:http://sqlfiddle.com/#!9/189e21f / 38

#1