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