Click here to Skip to main content
15,886,001 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
I'm using in mysql. I've created table "Sikcness" and I've added one record:

+--------+---------+---------+-------------+--------+----------+
| Id_SICK|ID_WORKER| BEGIN_DATE          | END_DATE            |
+--------+---------+---------+----------+------------+---------+
| 1      |   1     |2019-03-18 07:00:00  |2019-03-20 15:00:00  |  
+--------+---------+--------+------------+----------+----------+  


Then i'd like to sumerize in time (Time difference between End_date column and begin_date column) by the command:

SQL
SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(Sickness.END_DATE) - TIME_TO_SEC(Sickness.BEGIN_DATE))) AS 'SICKNESS TIME' FROM Sickness WHERE ID_WORKER = 1


But i have only that result (which is incorrect):

SICKNESS TIME
08:00:00


That command should count it like this:

+---------+-------------+--------+----------+
| BEGIN_DATE          | END_DATE            |
+---------+----------+------------+---------+
|2019-03-18 07:00:00  |2019-03-18 15:00:00  |  
+--------+------------+----------+----------+  
|2019-03-19 07:00:00  |2019-03-19 15:00:00  |  
+--------+------------+----------+----------+  
|2019-03-20 07:00:00  |2019-03-20 15:00:00  |  
+--------+------------+----------+----------+ 


Then it would be correct:
SICKNESS TIME
24:00:00


What kind of mysql query should i write? Any ideas? Greets.

What I have tried:

I've tried only with this command in below:

SQL
SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(Sickness.END_DATE) - TIME_TO_SEC(Sickness.BEGIN_DATE))) AS 'SICKNESS TIME' FROM Sickness WHERE ID_WORKER = 1


And it didn't work :(
Posted
Updated 15-Apr-19 0:27am
v2
Comments
Mohibur Rashid 18-Mar-19 6:22am    
Do it other way around, run sum on the diff and then convert back to day and hour

1 solution

Looking at the documentation, the TIME_TO_SEC function only calculates the number of seconds in the time value, not the date. You would need to add some code to calculate the number of days also. Take a look at MySQL DATEDIFF() Function[^]. Note that in the above case it would calculate the result as 2 days, so you need to adjust your answer accordingly.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900