2

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

I must find an alternative to this function because it's limited to '838:59:59' and I have more than 1000 hours.

Thank you

javacurve
  • 157
  • 1
  • 1
  • 13
  • check this question http://stackoverflow.com/questions/13733148/surpassing-mysqls-time-value-limit-of-8385959 – Jerko W. Tisler Apr 25 '14 at 12:51
  • Possible duplicate of [Surpassing MySQL's TIME value limit of 838:59:59](https://stackoverflow.com/questions/13733148/surpassing-mysqls-time-value-limit-of-8385959) – user2988142 Jan 11 '19 at 16:51

2 Answers2

3

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 ;

In action:

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)
fancyPants
  • 50,732
  • 33
  • 89
  • 96
1
SELECT @t;
+---------+
| @t      |
+---------+
| 3603750 |
+---------+

 SELECT CONCAT_WS(':'
                ,LPAD(FLOOR(@t/3600),2,0)
                ,LPAD(ROUND(FLOOR((@t/3600-FLOOR(@t/3600))*60)),2,'0')
                ,LPAD(ROUND((((@t/3600-FLOOR(@t/3600))*60) - FLOOR((@t/3600-FLOOR(@t/3600))*60)) * 60),2,0)
                )x;

+------------+
| x          |
+------------+
| 1001:02:30 |
+------------+
Strawberry
  • 33,750
  • 13
  • 40
  • 57