Changing Seconds to a Time in MySQL

The visitors table in our database comprises of id, country_code, date, and time columns filled with relevant data.

visitors table

id	country_code	date	time
1	AE	2023-05-10	1124
2	AU	2023-05-10	987
3	CA	2023-05-10	1003
4	SA	2023-05-10	1233

 

Obtain the country code along with the timed record of every visitors from the time column. The current format expresses it as a number of seconds; hence, let's showcase it as HH:MM:SS.

 

Let's see the solution

We shall utilize SEC_TO_TIME() function and here's the corresponding query you need to compose.

SELECT country_code,SEC_TO_TIME(time) AS visited_time FROM visitors;

Output:

country_code	visited_time
AE	00:18:44
AU	00:16:27
CA	00:16:43
SA	00:20:33

If you aim to showcase a certain number of seconds as a time value in MySQL, the SEC_TO_TIME() function is what you should employ. This function necessitates only one argument to run, specifically an expression that renders seconds as an integer or a column that stores seconds in an integer format. (In the illustrated case, we utilize the column time.)

The function SEC_TO_TIME() produces time in HH:MM:SS form, with hours represented from 00 to 59, minutes from 01 to 59, and seconds from 00 to 59.