MySQL: How to Convert Seconds To HH:MM:SS Format

In SQL language, SEC_TO_TIME() function allows to display seconds in a format: “HH:MM:SS” (hours, minutes and seconds). The function converts the number of seconds into the number of hours, minutes and seconds to be more understandable for a human.

This function is particularly useful to display the difference between 2 timestamps properly. It is also useful for re-displaying in the correct format times that would have been added together after being converted to seconds using the TIME_TO_SEC() function.
 

 

Syntax:

The function is used in an SQL query, using the following syntax:

SELECT SEC_TO_TIME( seconds );

In this query, the “seconds” parameter is the number of seconds to convert. The result will be displayed in “HH:MM:SS” format.
 

Example :

The function can be used with positive or negative numbers. When the limit is exceeded, the threshold of 838:59:59 will always be displayed.

SELECT SEC_TO_TIME(0);          -- output: 00:00:00
SELECT SEC_TO_TIME(1);          -- output: 00:00:01
SELECT SEC_TO_TIME(60);         -- output: 00:01:00
SELECT SEC_TO_TIME(3600);       -- output: 01:00:00
SELECT SEC_TO_TIME(86398);      -- output: 23:59:58
SELECT SEC_TO_TIME(86400);      -- output: 24:00:00
SELECT SEC_TO_TIME(3020400);    -- output: 838:59:59
SELECT SEC_TO_TIME(999999999);  -- output: 838:59:59
SELECT SEC_TO_TIME(-60);        -- retourne : -00:01:00
SELECT SEC_TO_TIME(-3600);      -- retourne : -01:00:00
SELECT SEC_TO_TIME(-86400);     -- retourne : -24:00:00
SELECT SEC_TO_TIME(-3020400);   -- retourne : -838:59:59
SELECT SEC_TO_TIME(-999999999); -- retourne : -838:59:59

MCQPractice competitive and technical Multiple Choice Questions and Answers (MCQs) with simple and logical explanations to prepare for tests and interviews.Read More