一张表的数据字段为三个 ID,begindate enddate
ID begindate enddate 计算时差为enddate-begindate
1 2010-10-07 07:50:30 2010-10-07 17:20:10 0天09:29:40
2 2010-10-08 09:20:15 2010-10-09 10:30:20 1天01:10:05
3 2010-10-09 08:10:10 2010-10-11 15:20:30 2天07:10:20
4 2010-10-10 07:10:06 2010-10-10 15:50:30 0天08:40:24
合计:4天02:40:29怎么可以用sql 语句求出合计数 4天02:40:29 ????
哪个高手知道,还望赐教,感激不尽!
ID begindate enddate 计算时差为enddate-begindate
1 2010-10-07 07:50:30 2010-10-07 17:20:10 0天09:29:40
2 2010-10-08 09:20:15 2010-10-09 10:30:20 1天01:10:05
3 2010-10-09 08:10:10 2010-10-11 15:20:30 2天07:10:20
4 2010-10-10 07:10:06 2010-10-10 15:50:30 0天08:40:24
合计:4天02:40:29怎么可以用sql 语句求出合计数 4天02:40:29 ????
哪个高手知道,还望赐教,感激不尽!
from 一张表
create table Ttime(ID int, begindate datetime, enddate datetime);
insert into tTime values(
1, '2010-10-07 07:50:30', '2010-10-07 17:20:10'),
(2 ,'2010-10-08 09:20:15', '2010-10-09 10:30:20'),
(3 ,'2010-10-09 08:10:10' ,'2010-10-11 15:20:30'),
(4, '2010-10-10 07:10:06' ,'2010-10-10 15:50:30')
select * from ttime;select FLOOR(sum(TIMESTAMPDIFF(second,begindate,enddate)) / (24*60*60)) as 'tian' into @T from ttime;
select FLOOR((sum(TIMESTAMPDIFF(second,begindate,enddate)) mod (24*60*60)) /(60*60)) as 'xiaoshi' into @H from ttime;
select floor((sum(TIMESTAMPDIFF(second,begindate,enddate))-@T*24*60*60-@h*60*60) /60 ) as 'fenzhong' into @M from ttime;
select floor(sum(TIMESTAMPDIFF(second,begindate,enddate))-@T*24*60*60-@h*60*60-@M*60) as 'miao' into @S from ttime;select concat(@T,'天',@H,'小时',@M,'分钟',@S,'秒');
mysql>
mysql> select concat(@T,'天',@H,'小时',@M,'分钟',@S,'秒');
+---------------------------------------------+
| concat(@T,'天',@H,'小时',@M,'分钟',@S,'秒') |
+---------------------------------------------+
| 4天2小时30分钟29秒 |
+---------------------------------------------+
1 row in set (0.00 sec)mysql>
SELECT ROUND(SUM(nt)/(24*3600),0) AS aday,
FLOOR((SUM(nt)- ROUND(SUM(nt)/(24*3600),0)*24*3600)/3600) AS ahour,
FLOOR((SUM(nt)-ROUND(SUM(nt)/(24*3600),0)*24*3600-FLOOR((SUM(nt)- ROUND(SUM(nt)/(24*3600),0)*24*3600)/3600)*3600)/60) AS AMIN,
SUM(NT)-(ROUND(SUM(nt)/(24*3600),0)*24*3600+FLOOR((SUM(nt)- ROUND(SUM(nt)/(24*3600),0)*24*3600)/3600)*3600+(FLOOR((SUM(nt)-ROUND(SUM(nt)/(24*3600),0)*24*3600-FLOOR((SUM(nt)- ROUND(SUM(nt)/(24*3600),0)*24*3600)/3600)*3600)/60))*60 ) AS Asec
FROM (
SELECT *,
HOUR(TIMEDIFF(enddate,begindate))*3600+MINUTE(TIMEDIFF(enddate,begindate))*60+SECOND(TIMEDIFF(enddate,begindate)) AS nt
FROM qt2) a;
我的博客,简单整理了一下mysql的日期和时间,希望对你有用