CODEWITHSUNDEEP
phpmysql
Jojo George
Jojo George

Reputation: 159

Subtracting sql time value with a given time value

My Query

 SELECT DATE, SEC_TO_TIME( SUM( TIME_TO_SEC(  `total_hours` ) ) ) AS total
FROM work_details
WHERE  `employee_id` =  '28'
AND DATE
BETWEEN  '2012-02-01'
AND  '2012-02-29'
GROUP BY DATE
ORDER BY DATE

// Which takes total hours worked for a given employee in given date from database table work_details ,total_hours is of time type.

Result

DATE    total
2012-02-01  04:19:50
2012-02-02  07:38:21
2012-02-06  07:43:24
2012-02-07  07:21:36
2012-02-08  07:38:45
2012-02-09  06:05:22
2012-02-10  07:28:00
2012-02-13  07:37:38
2012-02-14  07:37:22
2012-02-15  07:35:10
2012-02-16  07:29:13
2012-02-17  07:07:23
2012-02-20  07:40:32
2012-02-21  07:43:32
2012-02-22  07:36:40
2012-02-23  07:37:57
2012-02-24  07:35:04
2012-02-27  09:54:02
2012-02-28  07:41:35
2012-02-29  04:01:11

I need to retrieve the dates in which the the total working hours which are < 4 hours,so that i can count that days as half leaves. Any Help ??

I tried this query too, i have given 8 instead of 4,because my table consists no data less than 4 now.

SELECT DATE, SEC_TO_TIME( SUM( TIME_TO_SEC(  `total_hours` ) ) ) AS total, 
CASE WHEN SEC_TO_TIME( SUM( TIME_TO_SEC(  `total_hours` ) ) ) < ( 3600 *8 ) 
THEN 1 
ELSE 0 
END AS half_day
FROM work_details
WHERE  `employee_id` =  '28'
AND DATE
BETWEEN  '2012-02-01'
AND  '2012-02-29'
GROUP BY DATE

Result

DATE    total   half_day
2012-02-01  07:19:50    0
2012-02-02  07:38:21    0
2012-02-06  07:43:24    0
2012-02-07  07:21:36    0
2012-02-08  07:38:45    0
2012-02-09  06:05:22    0
2012-02-10  07:28:00    0
2012-02-13  07:37:38    0
2012-02-14  07:37:22    0
2012-02-15  07:35:10    0
2012-02-16  07:29:13    0
2012-02-17  07:07:23    0
2012-02-20  07:40:32    0
2012-02-21  07:43:32    0
2012-02-22  07:36:40    0
2012-02-23  07:37:57    0
2012-02-24  07:35:04    0
2012-02-27  09:54:02    0
2012-02-28  07:41:35    0
2012-02-29  08:01:11    0

Upvotes: 2

Views: 103

Answers (1)

Olivier Coilland
Olivier Coilland

Reputation: 3096

Try adding this at the end of the query:

HAVING HOUR(total) < 4;

Upvotes: 2

Related Questions