In this case in mysql database i've inserted new leave in "leave" table:
+--------+---------+---------+-------------+----------+--------------------------
|ID_LEAVE|ID_WORKER| FNAME | LNAME | BEGIN_DATE | END_DATE |
+--------+---------+---------+---------+-------------+--------------------+------
| 5 | 10 | MARIO | NEED |2019-03-22 07:00:00 |2019-03-25 15:00:00 |
+--------+---------+---------+-------------+----------+--------------------------
When I sumarize time of leave in mysql query in below:
SELECT leave.ID_LEAVE,
leave.ID_WORKER,
leave.BEGIN_DATE,
leave.END_DATE,
time_format(SUM((datediff(leave.END_DATE, leave.BEGIN_DATE) + 1) * (time(leave.END_DATE) - time(leave.BEGIN_DATE))), '%H:%i:%s') AS 'LEAVE TIME'
FROM leave
GROUP BY leave.ID_LEAVE
the i have reasult LEAVE TIME = 32:00:00
But i see it counts weekends (Saturdays and sundays) too. I've no idea how what should i change if could count without weekends. In this case Leave time should be 16:00:00. Can someone please what kind of query can I change. Thank you for any adivce. :)
You can use the following solution using a calendar table (based on this solution):
SELECT ID_LEAVE, SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF(TIME(end_date), TIME(begin_date)))))
FROM (
SELECT ADDDATE('1970-01-01', t4 * 10000 + t3 * 1000 + t2 * 100 + t1 * 10 + t0) AS date_value
FROM
(SELECT 0 t0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t0,
(SELECT 0 t1 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t1,
(SELECT 0 t2 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t2,
(SELECT 0 t3 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t3,
(SELECT 0 t4 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t4
) calendar INNER JOIN `leave` ON calendar.date_value BETWEEN DATE(leave.BEGIN_DATE) AND DATE(leave.END_DATE)
WHERE NOT WEEKDAY(date_value) IN (5, 6)
GROUP BY ID_LEAVE