该楼层疑似违规已被系统折叠 隐藏此楼查看此楼
DELIMITER//
CREATE FUNCTION f_IntToStrdate(sec BIGINT )
RETURNS text
BEGIN
DECLARE resstr text;
DECLARE y text;
DECLARE m VARCHAR(5);
DECLARE d VARCHAR(5);
DECLARE h VARCHAR(5);
DECLARE i VARCHAR(5);
DECLARE s VARCHAR(5);
-- 判断前置0
-- 判断秒数
IF sec%60<10 THEN
SET s=CONCAT('0',sec%60,'秒');
ELSE
SET s=CONCAT(sec%60,'秒');
END IF;
-- 判断分数
IF FLOOR(sec/60)%60 < 10 THEN
SET i=CONCAT('0',FLOOR(sec/60)%60,'分钟');
ELSE
SET i=CONCAT(FLOOR(sec/60)%60,'分钟');
END IF;
-- 判断小时
IF FLOOR(sec/3600)%24 < 10 THEN
SET h=CONCAT('0',FLOOR(sec/3600)%24,'小时');
ELSE
SET h=CONCAT(FLOOR(sec/3600)%24,'小时');
END IF;
-- 判断天数
IF FLOOR(sec/86400)%30 < 10 THEN
SET d=CONCAT('0',FLOOR(sec/86400)%30,'天');
ELSE
SET d=CONCAT(FLOOR(sec/86400)%30,'天');
END IF;
-- 判断月份
IF FLOOR(sec/2678400)%12 < 10 THEN
SET m=CONCAT('0',FLOOR(sec/2678400)%12,'个月');
ELSE
SET m=CONCAT(FLOOR(sec/2678400)%12,'个月');
END IF;
-- 判断年份
SET y=CONCAT(FLOOR(sec/31536000),'年');
-- 进行拼接
SET resstr=CONCAT(
CASE WHEN FLOOR(sec/31536000)=0 THEN '' ELSE y END ,
CASE WHEN m='00个月' THEN '' ELSE m END ,
CASE WHEN d='00天' THEN '' ELSE d END ,
CASE WHEN h='00时' THEN '' ELSE h END ,
CASE WHEN i='00分钟' THEN '' ELSE i END ,
CASE WHEN s='00秒' THEN '' ELSE s END
);
RETURN resstr;
END//
DELIMITER;