一张表的数据字段为三个 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>
insert into tTime values(
Query OK, 0 rows affected (0.00 sec)[email protected]>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') ;
ERROR 1146 (42S02): Table 'test.tTime' doesn't exist
[email protected]>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');
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: [email protected]>select DATE_FORMAT(FROM_UNIXTIME(sum(UNIX_TIMESTAMP(enddate)-UNIX_TIMESTAMP(begindate))),'%d天%H:%i:%s')
-> ;from tTime
ERROR 1054 (42S22): Unknown column 'enddate' in 'field list'
-> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'from tTime' at line 1
[email protected]>select DATE_FORMAT(FROM_UNIXTIME(sum(UNIX_TIMESTAMP(enddate)-UNIX_TIMESTAMP(begindate))),'%d天%H:%i:%s') from Ttime;
+----------------------------------------------------------------------------------------------------+
| DATE_FORMAT(FROM_UNIXTIME(sum(UNIX_TIMESTAMP(enddate)-UNIX_TIMESTAMP(begindate))),'%d天%H:%i:%s') |
+----------------------------------------------------------------------------------------------------+
| 05天10:30:29 |
+----------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
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;
from_unixtime和我们差8个小时。
但是如果把时区改了以后,再测试一下:mysql> select from_unixtime(0);
+---------------------+
| from_unixtime(0) |
+---------------------+
| 1970-01-01 00:00:00 |
+---------------------+
1 row in set (0.00 sec)发现对应的是一月一日。
所以当时间为0的时候date也会是1,所以这样就会多出一天吧
但是如果日期超过30的话 也会有问题
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 concat(floor(time_format(SEC_TO_TIME(sum(UNIX_TIMESTAMP(enddate)-UNIX_TIMESTAMP(begindate))),'%H')/24),'天',mod(time_format(SEC_TO_TIME(sum(UNIX_TIMESTAMP(enddate)-UNIX_TIMESTAMP(begindate))),'%H'),24),time_format(SEC_TO_TIME(sum(UNIX_TIMESTAMP(enddate)-UNIX_TIMESTAMP(begindate))),':%i:%s')) as passtime from ttime;+------------+
| passtime |
+------------+
| 4天2:30:29 |
+------------+
SEC_TO_TIME(seconds)
这个函数用得太好了。。受教了。
嘿嘿 这样简便多了 谢谢各位大侠
SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF(enddate,begindate))))
FROM TableName1