mysql - 在mariadb中计算时间时返回null

Question

我有下表

CREATE TABLE T_WorkContents (
user_id varchar(5),
working_date DATE NOT NULL,
working_start_time TIME NOT NULL,
working_end_time TIME NOT NULL,
break_time TIME DEFAULT '0',
holiday_mark tinyint DEFAULT '0',
overtime_hours TIME DEFAULT '0',
overtime_reason TEXT,
work_detail TEXT,
remarks TEXT,
regist_user varchar(5),
regist_date DATETIME,
update_user varchar(5),
update_date DATETIME,
delete_flag tinyint DEFAULT '0', 
PRIMARY KEY(user_id, working_date),
FOREIGN KEY(user_id) REFERENCES T_UserInfo(user_id)
);

CREATE TABLE T_UserInfo (
user_id varchar(5) NOT NULL,
user_name varchar(30),
password varchar(20) NOT NULL,
authority tinyint NOT NULL DEFAULT '1',
regist_user varchar(5),
regist_date datetime,
update_user varchar(5),
update_date datetime,
delete_flag tinyint NOT NULL DEFAULT '0',
PRIMARY KEY(user_id),
FOREIGN KEY(authority) REFERENCES T_Authority(authority)
);

和这样的sql脚本

SELECT t.user_id, u.user_name, SUM(TIMESTAMPDIFF(HOUR,     TIME(t.working_start_time), TIME(t.working_end_time))) AS total_time, SUM(TIME(TIMESTAMPDIFF(HOUR, TIME(CONVERT('00:00:00',TIME)), TIME(t.overtime_hours)))) AS overtime , SUM(TIME(TIMESTAMPDIFF(HOUR, TIME(CONVERT('00:00:00',TIME)), Time(t.break_time))) )AS break_time , COUNT(*) AS count_day ,MONTH(t.working_date) as working_month,YEAR(t.working_date) AS working_year FROM T_WorkContents AS t INNER JOIN T_UserInfo AS u ON u.user_id = t.user_id WHERE t.delete_flag = 0 AND u.delete_flag = 0  AND MONTH(t.working_date) = 4 AND YEAR(t.working_date) = 2015 GROUP BY t.user_id, working_month ,working_year;

执行脚本后，我得到以下结果

这里可能出了什么问题？

Answer 1

TIMESTAMPDIFF() 需要日期时间，而不是时间值，TIME() 返回字符串，而不是时间。尝试使用 TIMEDIFF() 函数，并将其用于字段本身，而不是 TIME() 函数结果