如何使用SQL查询计算时差

Question

我有一个名为tblemptimelog的SQL表，包含以下列：

empid-INT工作日期-日期timein1-时间timeout1-时间timein2-时间timeout2-时间

我的查询：

"SELECT * FROM tblemptimelog
WHERE empid = 1111
ORDER BY workdate";

结果显示如下：

|Emp ID | Date       | Time In  | Time Out | Time In  | Time Out
|1111   | 04-18-2020 | 08:00:00 | 12:00:00 | 13:10:05 | 17:00:00
|1111   | 04-19-2020 | 08:00:00 | 12:00:00 | 13:00:00 | 17:00:00

我想显示timein1和timeout1之间的时间差。

以及timein2和timeout2之间的时间差如下：

|Emp ID | Date       | Time In  | Time Out | Time In  | Time Out | Total Time
|1111   | 04-18-2020 | 08:00:00 | 12:00:00 | 13:10:05 | 17:00:00 | 07:49:55
|1111   | 04-19-2020 | 08:00:00 | 12:00:00 | 13:00:00 | 17:00:00 | 08:00:00

我还想显示总时间。在这种情况下，为15:49:55

Total: 15:49:55

我如何实现既定目标？

Answer 1

要回答第一部分，可以使用timediff & addtime

我想显示时间1和timeout1和timein2和timeout2之间的时间差

select
a.*,
TIMEDIFF(time_out_1, time_in_1) diff_time_1,
TIMEDIFF(time_out_2, time_in_2) diff_time_2,
ADDTIME( TIMEDIFF(time_out_1, time_in_1), TIMEDIFF(time_out_2, time_in_2)) diff_time_1_and_2
from
( 
select  TIME('08:00:00') time_in_1, TIME('12:00:00') time_out_1, TIME('13:10:05') time_in_2, TIME('17:00:00') time_out_2
union all  
select  TIME('08:00:00') time_in_1, TIME('12:00:00') time_out_1, TIME('13:00:00') time_in_2, TIME('17:00:00') time_out_2
) as a

结果

time_in_1|time_out_1|time_in_2|time_out_2|diff_time_1|diff_time_2|diff_time_1_and_2|
---------|----------|---------|----------|-----------|-----------|-----------------|
08:00:00|  12:00:00| 13:10:05|  17:00:00|   04:00:00|   03:49:55|         07:49:55|
08:00:00|  12:00:00| 13:00:00|  17:00:00|   04:00:00|   04:00:00|         08:00:00|

要回答第二部分，您可以使用SEC_TO_TIME(SUM(TIME_TO_SEC(time)))转换为秒，然后求和，然后返回时间

此外，在表格之后，我想显示总时间。在这种情况下将是15:49:55

总计：15:49:55

select SEC_TO_TIME(SUM(TIME_TO_SEC(diff_time_1_and_2))) total_time  from ( 
select
a.*,
TIMEDIFF(time_out_1, time_in_1) diff_time_1,
TIMEDIFF(time_out_2, time_in_2) diff_time_2,
ADDTIME( TIMEDIFF(time_out_1, time_in_1), TIMEDIFF(time_out_2, time_in_2)) diff_time_1_and_2
from
( 
select  TIME('08:00:00') time_in_1, TIME('12:00:00') time_out_1, TIME('13:10:05') time_in_2, TIME('17:00:00') time_out_2
union all  
select  TIME('08:00:00') time_in_1, TIME('12:00:00') time_out_1, TIME('13:00:00') time_in_2, TIME('17:00:00') time_out_2
) as a
) as b

结果

total_time|
----------|
15:49:55|