Calculate time difference in sql

1

Using the data below I am trying to calculate the difference between timein1 and timeout1, calculate difference between timein2 and timeout2, add the differences together to get the total hh:mm:ss per row and then calculate combined total hh:mm:ss for the entire query. I have managed to retrieve everything but the total time.

** SAMPLE DATA *******************************
CREATE TABLE tblemptimelog (
recid INTEGER,
empid INTEGER,
workdate DATE,
timein1 TIME,
timeout1 TIME,
timein2 TIME,
timeout2 TIME,
override VARCHAR(255)
) ENGINE=myisam DEFAULT CHARSET=utf8;

SET autocommit=1;

INSERT INTO tblemptimelog () VALUES (1, 1832, '2020-04-13', '08:00:00', '12:00:00', '13:10:05', '17:00:00', NULL);
INSERT INTO tblemptimelog () VALUES (2, 1832, '2020-04-14', '08:00:00', '12:00:00', '13:00:00', '21:00:00', NULL);
INSERT INTO tblemptimelog () VALUES (3, 1832, '2020-04-15', '08:00:00', '12:00:00', '13:00:00', NULL, NULL);
INSERT INTO tblemptimelog () VALUES (4, 1832, '2020-04-16', '08:00:00', '12:00:00', '13:00:00', '17:00:00', NULL);
INSERT INTO tblemptimelog () VALUES (5, 1832, '2020-04-17', '08:00:00', NULL, NULL, NULL, NULL);
INSERT INTO tblemptimelog () VALUES (6, 1832, '2020-04-18', '08:00:00', '12:00:00', '13:00:00', NULL, NULL);
INSERT INTO tblemptimelog () VALUES (7, 1831, '2020-04-19', '08:00:00', '12:00:00', '13:00:00', '23:00:00', NULL);

Here is the query I have so far:

SELECT *,
ADDTIME(TIMEDIFF(timeout1, timein1), TIMEDIFF(timeout2, timein2)) diff_time_1_and_2
FROM tblemptimelog
WHERE empid = 1832
ORDER BY workdate

The results are put into a table on a web page and displayed as:

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

This is working as it should. I am running into problems totaling the diff_time_1_and_2 column which should total 27:49:55, which will be displayed at the end of the table as shown below:

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

**TOTAL TIME: 27:49:55**

I tried the following query:
SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(diff_time_1_and_2))) total_time from ( SELECT ADDTIME( TIMEDIFF(timeout1, timein1), TIMEDIFF(timeout2, timein2)) diff_time_1_and_2 FROM tblemptimelog WHERE empid = 1832 ORDER BY workdate )

and got an error:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'LIMIT 0, 25' at line 1

I also tried the following query:
SELECT *, ADDTIME(TIMEDIFF(timeout1, timein1), TIMEDIFF(timeout2, timein2)) diff_time_1_and_2, SEC_TO_TIME(SUM(TIME_TO_SEC(diff_time_1_and_2))) total_time FROM tblemptimelog WHERE empid = 1832 ORDER BY workdate

and I got the following error:
#1054 - Unknown column 'diff_time_1_and_2' in 'field list'

I have tried variations of the last 2 queries, but I still get one of the two errors above.

I hope I included enough information for someone to give me assistance.

2

What database product is this for? Ms sql or ...

3

Hi

Hope this helps !!

Just do total separately .. and then do UNION ALL

Example ..

create table Sample
(
id int
,fruit varchar(20)
,price int
)
go

insert into Sample SELECT 1,'apple',10
insert into Sample SELECT 2,'banana',20

go

select id,fruit,price from Sample
union all
select null, null, sum(price) from Sample

4

Definitely looks like MySQL to me.

5

It's MYSQL

6

I will give it a try. Thank you for the response

7

This forum is for microsoft sql server. But there might be some here that are familiar with that product

8

First, I want to thank you for the example. I tried changing to my retrieve my data using the following query:
SELECT recid, empid, workdate, timein1, timeout1, timein2, timeout2,
ADDTIME(TIMEDIFF(timeout1, timein1), TIMEDIFF(timeout2, timein2)) diff_time_1_and_2
FROM tblemptimelog WHERE empid = 1832
UNION ALL
SELECT recid, empid, workdate, timein1, timeout1, timein2, timeout2,
SEC_TO_TIME(SUM(TIME_TO_SEC(diff_time_1_and_2))) total_time
FROM tblemptimelog WHERE empid = 1832

I still got the following error:
#1054 - Unknown column 'diff_time_1_and_2' in 'field list'

9

it needs to have nulls

select 1,2,3
union all
select null,null,sum(3)

10

I code the query to:
SELECT recid, empid, workdate, timein1, timeout1, timein2, timeout2,
ADDTIME(TIMEDIFF(timeout1, timein1), TIMEDIFF(timeout2, timein2)) diff_time_1_and_2
FROM tblemptimelog WHERE empid = 1832
UNION ALL
SELECT null, null, null, null, null, null, null,
SEC_TO_TIME(SUM(TIME_TO_SEC(diff_time_1_and_2))) total_time
FROM tblemptimelog WHERE empid = 1832

I received the same error:
#1054 - Unknown column 'diff_time_1_and_2' in 'field list'

11

You dont have a column called diff_time_1_and_2

12

hi

you are doing a UNION ALL

you are using a column alias (diff_time_1_and_2)
from the top part of union
in the bottom part of union

That will not work ...you cannot use column aliases
use the columns

that's why its complaining !!

use this in bottom part of union
SEC_TO_TIME(SUM(TIME_TO_SEC(ADDTIME(TIMEDIFF(timeout1, timein1), TIMEDIFF(timeout2, timein2)) ))) total_time