CODEWITHSUNDEEP
mysqldatetimedifference
Prochu1991
Prochu1991

Reputation: 453

How to sumarize corectly datetime (difference between end_date and begin_date) - mysql

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.

Upvotes: 0

Views: 55

Answers (2)

realmfoo
realmfoo

Reputation: 433

Difference in hours you should multiply by difference in days:

mysql> SELECT SEC_TO_TIME(SUM((DATEDIFF(end_date, begin_date) + 1) * (TIME_TO_SEC(END_DATE) - TIME_TO_SEC(BEGIN_DATE)))) AS 'SICKNESS TIME' FROM Sickness WHERE ID_WORKER = 1;
+---------------+
| SICKNESS TIME |
+---------------+
| 24:00:00      |
+---------------+
1 row in set (0.00 sec)

Another query with TIME_FORMAT:

mysql> select time_format(SUM((datediff(end_date, begin_date) + 1) * (time(end_date) - time(begin_date))), '%H:%i:%s') as 'SICKNESS TIME' from Sickness where id_worker = 1;
+---------------+
| SICKNESS TIME |
+---------------+
| 24:00:00      |
+---------------+
1 row in set (0.00 sec)

You can try this query on SQL Fiddle

Upvotes: 1

Darshan Mehta
Darshan Mehta

Reputation: 30809

So, you need to calculate it like this:

  1. Number of hours on the first day of sickness
  2. Number of hours on last day of sickness
  3. Number of days in sickness multiplied by 8 (i.e. number of working hours)

Once done, you need to add these up to get the result, e.g.

SELECT
TIMESTAMPDIFF(HOUR, begin_date, CONCAT(DATE(begin_date), ' 15:00:00')) +
(TIMESTAMPDIFF(DAY, begin_date, end_date) - 1) * 8 +
TIMESTAMPDIFF(HOUR, CONCAT(DATE(end_date), ' 07:00:00'), end_date) AS time 
FROM test
WHERE worker = 1;

Here's the SQL Fiddle.

Upvotes: 0

Related Questions