1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- ----------------------------
-- Table structure for user_working_hours
-- ----------------------------
DROP TABLE IF EXISTS user_working_hours;
CREATE TABLE user_working_hours (
user_working_hour_id int(11) NOT NULL AUTO_INCREMENT,
user_id int(11) DEFAULT NULL,
date_start datetime DEFAULT NULL,
date_end datetime DEFAULT NULL,
PRIMARY KEY (user_working_hour_id)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
-- ----------------------------
-- Records of user_working_hours
-- ----------------------------
INSERT INTO user_working_hours VALUES ('1', '1', '2014-10-16 05:44:01', '2014-10-16 10:53:05');
INSERT INTO user_working_hours VALUES ('2', '1', '2014-10-16 05:51:29', '2014-10-16 11:48:53');
INSERT INTO user_working_hours VALUES ('4', '1', null, '2014-10-16 05:52:10');
INSERT INTO user_working_hours VALUES ('5', '1', '2014-10-16 12:16:18', null);
count the hours for specific user via :
1
2
3
4
5
SELECT FLOOR(SUM(t)/3600)
FROM (
SELECT TIME_TO_SEC(TIMEDIFF(date_end,date_start)) as t
FROM user_working_hours where user_id=1
) hours;
the records with ID 4,5 will be ignored, because the TIMEDIFF result will be null. With FLOOR the result is : 
without use FLOOR + donot convert to hour, aka by MYSQL return seconds, then at PHP
1
2
3
4
$hours = floor($seconds / 3600);
$mins = floor(($seconds - ($hours*3600)) / 60);
$secs = floor($seconds % 60);
echo $hours . "h " . $mins . "m ".$secs."s"
origin - http://www.pipiscrew.com/?p=1530 mysql-sum-timediff-by-recordset