Articles Cookbook
Back to list
MySQL

How to Change Seconds to a Time Value in MySQL

Database:

Operators:

SEC_TO_TIME()

Problem:

You’d like to display a number of seconds as a time value in hours, minutes, and seconds in MySQL.

Example:

Our database has a table named athlete_score with data in the columns id, first_name, last_name, and score_seconds.

idfirst_namelast_namescore_seconds
1EmilyWatson1124
2TomGarcia987
3GaryMartinez1003
4JamesAnderson1233

For each athlete, let’s get their first and last names and their record time from the seconds_record column. Right now, it’s stored as a number of seconds; let’s display it in HH:MM:SS format.

Solution:

We’ll use the SEC_TO_TIME() function. Here’s the query you would write:

SELECT first_name,
       last_name,
       SEC_TO_TIME(score_seconds)
         AS  score_time
FROM athlete_score;

Here’s the result of the query:

first_namelast_namescore_time
EmilyWatson00:18:44
TomGarcia00:16:27
GaryMartinez00:16:43
JamesAnderson00:20:33

Discussion:

Use the SEC_TO_TIME() function if you want to display a number of seconds as a time value in MySQL. This function takes only one argument – either an expression that returns seconds as an integer or a column storing a number of seconds as an integer. (In our example, we use the column score_seconds.)

SEC_TO_TIME() returns the time in the HH:MM:SS format, where HH is an hour from 00 to 59, MM are minutes from 01 to 59, and SS are seconds from 00 to 59. In our example, Emily Watson score in seconds is 1124 seconds; as a time value, it is 18 minutes and 44 seconds.

Recommended courses:

Recommended articles:

See also:

go to top