mysql怎么截取时分秒_mysql函数 秒转换成时分秒

mysql函数 秒转换成时分秒

发布时间：2018-08-21 17:04,

浏览次数：1582

, 标签：

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

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