如何使用 MYSQL 将秒转换为时间 [英] How to convert second to Time using MYSQL
本文介绍了如何使用 MYSQL 将秒转换为时间的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
您能帮我在 sql 查询中将秒转换为时间吗?我正在使用 MySql 函数 SEC_TO_TIME.
Could you please help me to convert second to time in a sql query. I'm using MySql function SEC_TO_TIME.
TIME_FORMAT(SEC_TO_TIME(COALESCE(ROUND(NBSECONDS), 0)),'%H:%i:%s') AS MYTIME
我必须找到这个函数的替代方法,因为它仅限于838:59:59",而且我有 1000 多个小时.
I must find an alternative to this function because it's limited to '838:59:59' and I have more than 1000 hours.
谢谢
推荐答案
你可以像这样在 MySQL 中简单地编写你自己的函数:
You can simply write your own function in MySQL like this:
drop function if exists my_sec_to_time;
delimiter $$
create function my_sec_to_time(p_sec int)
returns varchar(10)
deterministic
begin
declare v_hour int;
declare v_minute int;
declare v_tmp_sec int;
set v_hour = floor(p_sec / 3600);
set v_tmp_sec = p_sec - (v_hour * 3600);
set v_minute = floor(v_tmp_sec / 60);
set v_tmp_sec = v_tmp_sec - (v_minute * 60);
return concat(v_hour, ':', v_minute, ':', v_tmp_sec);
end $$
delimiter ;
在行动:
mysql;root@localhost(playground)> select my_sec_to_time(3020399);
+-------------------------+
| my_sec_to_time(3020399) |
+-------------------------+
| 838:59:59 |
+-------------------------+
1 row in set (0.00 sec)
mysql;root@localhost(playground)> select my_sec_to_time(3020400);
+-------------------------+
| my_sec_to_time(3020400) |
+-------------------------+
| 839:0:0 |
+-------------------------+
1 row in set (0.00 sec)
mysql;root@localhost(playground)> select my_sec_to_time(4020400);
+-------------------------+
| my_sec_to_time(4020400) |
+-------------------------+
| 1116:46:40 |
+-------------------------+
1 row in set (0.00 sec)
这篇关于如何使用 MYSQL 将秒转换为时间的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文