mysql函数秒转换成时分秒

方法一：

CREATE DEFINER=`root`@`%` FUNCTION `timeDifference`(Dirty VARCHAR ( 500 ))
RETURNS text CHARSET utf8
DETERMINISTIC
BEGIN
IF Dirty<0 THEN
SET Dirty= TRIM(LEADING '-' FROM Dirty);
SET Dirty = CONCAT('-',
cast( FLOOR(Dirty / 86400)*24+(Dirty % 86400 / 3600) AS signed INTEGER ),
'时',
cast( Dirty % 86400 % 3600 / 60 AS signed INTEGER ),
'分',
cast( Dirty % 86400 % 3600 % 60 AS signed INTEGER ),
'秒'
);
ELSE
SET Dirty = CONCAT(
cast( FLOOR(Dirty / 86400)*24+(Dirty % 86400 / 3600) AS signed INTEGER ),
'时',
cast( Dirty % 86400 % 3600 / 60 AS signed INTEGER ),
'分',
cast( Dirty % 86400 % 3600 % 60 AS signed INTEGER ),
'秒'
);
END IF;
RETURN Dirty;

END

注：这种计算有问题9728秒时，转换成时分秒有一小时误差。别的没发现问题！应该是小数点后面死循环的原因，有大神发现其他原因请留言。

方法二：

CREATE DEFINER=`root`@`localhost` FUNCTION `timeDifference`(Dirty VARCHAR (
500 )) RETURNS text CHARSET utf8
DETERMINISTIC
BEGIN
DECLARE a integer(10);
DECLARE b integer(10);
DECLARE c integer(10);
DECLARE d integer(10);
DECLARE e integer(10);
DECLARE f integer(10);
DECLARE timeDif VARCHAR ( 500 );
set a=0;
set d=0;
set f=0;
IF Dirty<0 THEN
SET timeDif= TRIM(LEADING '-' FROM Dirty);
ELSE
SET timeDif=Dirty;
END IF;
   set a =cast( timeDif % 60 AS signed INTEGER ) ;#秒
set b =timeDif-a;#时间差
   IF b > 0 THEN
   set c=cast( b / 60 AS signed INTEGER );
   set d=cast( c % 60 AS signed INTEGER );#分
   set e=c-d;
END IF;
IF e > 0 THEN
   set f = cast( e / 60 AS signed INTEGER );#时
END IF;
IF Dirty < 0 THEN
   set Dirty=CONCAT('-',f,'时',d,'分',a,'秒');
ELSE
set Dirty=CONCAT(f,'时',d,'分',a,'秒');
END IF;
RETURN Dirty;

END

这种计算目前没发现问题。有更好的请留言。

热门工具换一换

mysql函数 秒转换成时分秒