sql秒转时分秒格式,时分秒转秒数(内置函数、自定义函数，及变量形式)

sec_to_time 内置函数

上限为838:59:59，无法解析更大时间，因此可能需要重写该函数

select sec_to_time(80) # > 00:01:20

重写函数

CREATE FUNCTION `BIG_SEC_TO_TIME`(
		secondes bigint(20)
) 
	RETURNS varchar(200) CHARSET utf8 
	#SQL SECURITY INVOKER 
begin
DECLARE result varchar(200);
DECLARE str_sec int(11);
DECLARE str_minute int(11);
DECLARE str_hour bigint(20);

if secondes is null then
	set result='00:00:00';
elseif secondes=0  then
	set result='00:00:00';
else
	set secondes=abs(secondes);
	set str_sec = mod(secondes,60);
    set str_minute = floor(mod(secondes/60,60));
    set str_hour = floor(secondes/3600);
	set result=concat(str_hour,':',str_minute,':',str_sec);
end if;

return result;
end 

用法如下

select BIG_SEC_TO_TIME(80) # > 0:1:20

美化上述函数格式

CREATE FUNCTION `BIG_SEC_TO_TIME`(
		secondes bigint(20)
) 
	RETURNS varchar(200) CHARSET utf8 
	#SQL SECURITY INVOKER 
begin
DECLARE result varchar(200);
DECLARE str_sec int(11);
DECLARE str_minute int(11);
DECLARE str_hour bigint(20);

if secondes is null then
	set result='00:00:00';
elseif secondes=0  then
	set result='00:00:00';
else
	set secondes=abs(secondes);
	set str_sec = mod(secondes,60);
    set str_minute = floor(mod(secondes/60,60));
    set str_hour = floor(secondes/3600);
	set result=concat(if(str_hour>9,str_hour,concat('0',str_hour)),':',if(str_minute >9,str_minute ,concat('0',str_minute )),':',if(str_sec >9,str_sec ,concat('0',str_sec )));
end if;

return result;
end 

示例如下：

变量方式，求得时间

set @time=1000;

select concat(if(FLOOR(@time/3600)>9,FLOOR(@time/3600),concat("0",FLOOR(@time/3600))),":",if(FLOOR(@time%3600/60)>9,FLOOR(@time%3600/60),concat("0",FLOOR(@time%3600/60))),":",if(FLOOR(@time%3600%60)>9,FLOOR(@time%3600%60),concat("0",FLOOR(@time%3600%60))))