处理时间问题的函数很多,下面举例简单说几个常用的:
SELECT TIMESTAMPDIFF(YEAR,'2003-02-01','2005-05-01') AS 时间差(年);
SELECT TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01') AS 时间差(月);
SELECT TIMESTAMPDIFF(DAY,'2003-02-01','2003-05-01') AS 时间差(日);
SELECT TIMESTAMPDIFF(HOUR,'2003-02-01','2003-05-01') AS 时间差(小时);
SELECT TIMESTAMPDIFF(MINUTE,'2003-02-01','2003-05-01') AS 时间差(分钟);
SELECT TIMESTAMPDIFF(SECOND,'2003-02-01','2003-05-01') AS 时间差(秒);
执行结果依次如下:
时间格式化
SELECT DATE_FORMAT('2003-02-01 13:05:30','%Y-%m-%d %H:%i:%S')
SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%S')
SELECT DATE_FORMAT(CURDATE(),'%Y-%m-%d %H:%i:%S')
返回时间格式为:00:00:00
sec_to_time(sum(time_to_sec(sd.`endtime`)-time_to_sec(sd.`starttime`))) 持续时长,