How to Change Seconds to a Time Value in MySQL

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.

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:

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.