php - 用给定的时间值减去sql时间值

Question

我的查询

 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

//从数据库表 work_details 中获取给定员工在给定日期的总工作小时数，total_hours 是时间类型。

结果

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

我需要检索总工作时间为 < 4 小时的日期，以便我可以将这些天计算为一半的假期。有什么帮助吗？？

我也试过这个查询，我给了 8 而不是 4，因为我的表现在不包含少于 4 的数据。

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

结果

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

Answer 1

尝试在查询末尾添加:

HAVING HOUR(total) < 4;