mysql创建时间距离函数

param_createTime传入的创建时间

param_distanceday传入的过期天数

CREATE FUNCTION f_getLeftTime(param_createTime VARCHAR(20),param_distanceday INT)

    
       RETURNS VARCHAR(10)
     BEGIN


      DECLARE param_second BIGINT;
DECLARE param_minute BIGINT;
DECLARE param_hour BIGINT;
DECLARE param_day BIGINT;
DECLARE param_month BIGINT;
DECLARE param_year BIGINT;
DECLARE param_return VARCHAR(10) DEFAULT '订单已过时';


SET param_second = UNIX_TIMESTAMP(DATE_ADD(param_createTime,INTERVAL param_distanceday DAY))-UNIX_TIMESTAMP(NOW());
SET param_minute = floor(param_second/60);
SET param_hour = floor(param_minute/60);
SET param_day = floor(param_hour/24);
SET param_month = floor(param_day/30);

IF param_day>0 THEN RETURN CONCAT('还剩',param_day,'天',(param_hour-param_day*24),'小时');
ELSEIF param_hour>0 THEN RETURN CONCAT('还剩',param_hour,'小时',(param_minute-param_hour*60),'分钟');
ELSEIF param_minute>0 THEN RETURN CONCAT('还剩',param_minute,'分钟',(param_second-param_minute*60),'秒');
ELSE RETURN param_return;
END IF; 
    
    END