CODEWITHSUNDEEP
mysql
mjpriest65
mjpriest65

Reputation: 31

How to calculate the time difference using a SQL query?

I have a SQL table named tblemptimelog with the following columns:

empid - INT workdate - DATE timein1 - TIME timeout1 - TIME timein2 - TIME timeout2 - TIME

My query:

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

The results displayed as follows:

|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

I want to display the time difference between timein1 and timeout1.

As well as the time difference between timein2 and timeout2 for as follows:

|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

I also want to display the total time. In this case, it would be 15:49:55

Total: 15:49:55

How do I achieve my set goal?

Upvotes: 2

Views: 123

Answers (1)

Omari Victor Omosa
Omari Victor Omosa

Reputation: 2879

To answer your first part you may use timediff & addtime

I am wanting to display the time difference between timein1 and timeout1 and the time difference between timein2 and 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

result

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|

To answer your second part you may use SEC_TO_TIME(SUM(TIME_TO_SEC(time))) to convert to seconds then sum, then return to time

Also, after the table, I want to display the total time. In this case it would be 15:49:55

Total: 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

result

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

Upvotes: 1

Related Questions