How to use the MySQL SEC_TO_TIME() function

In this article, we will learn how to use the MySQL SEC_TO_TIME() function, which converts a number of seconds to a time value.

Posted on

In this article, we will learn how to use the MySQL SEC_TO_TIME() function, which converts a number of seconds to a time value. We will also see some examples of how to use this function in different situations, and explore some related functions that can be helpful for working with seconds and time values.

Syntax

The syntax of the SEC_TO_TIME() function is as follows:

SEC_TO_TIME(seconds)

The seconds parameter can be any numeric expression that represents the number of seconds. If the seconds parameter is NULL, the function returns NULL. The SEC_TO_TIME() function returns a time value in the format of ‘HH:MM:SS’ or ‘HHH:MM:SS’, depending on whether the number of hours is less than or equal to 99 or greater than 99. The function also handles negative values and fractional seconds.

Examples

Let’s see some examples of how to use the SEC_TO_TIME() function in MySQL.

Example 1: Convert a number of seconds to a time value

We can use the SEC_TO_TIME() function to convert a number of seconds to a time value. For example:

SELECT SEC_TO_TIME(3600) AS result;

This query will convert 3600 seconds to a time value. The query will return ‘01:00:00’, which represents one hour.

Example 2: Convert a negative number of seconds to a time value

We can use the SEC_TO_TIME() function to convert a negative number of seconds to a time value. For example:

SELECT SEC_TO_TIME(-3600) AS result;

This query will convert -3600 seconds to a time value. The query will return ‘-01:00:00’, which represents negative one hour.

Example 3: Convert a fractional number of seconds to a time value

We can use the SEC_TO_TIME() function to convert a fractional number of seconds to a time value. For example:

SELECT SEC_TO_TIME(3600.5) AS result;

This query will convert 3600.5 seconds to a time value. The query will return ‘01:00:00.5’, which represents one hour and half a second.

Example 4: Convert a large number of seconds to a time value

We can use the SEC_TO_TIME() function to convert a large number of seconds to a time value. For example:

SELECT SEC_TO_TIME(100000) AS result;

This query will convert 100000 seconds to a time value. The query will return ‘27:46:40’, which represents 27 hours, 46 minutes, and 40 seconds.

Example 5: Convert a number of seconds to a time value and format it

We can use the SEC_TO_TIME() function with the TIME_FORMAT() function, which returns the time value formatted according to a specified format, to convert a number of seconds to a time value and format it. For example:

SELECT TIME_FORMAT(SEC_TO_TIME(3600), '%H hours %i minutes %s seconds') AS result;

This query will convert 3600 seconds to a time value and format it. The query will return ‘01 hours 00 minutes 00 seconds’, which represents one hour in a more readable format.

There are some other functions that are related to the SEC_TO_TIME() function, and can be useful for working with seconds and time values. Here are some of them:

  • TIME_TO_SEC(): This function converts a time value to a number of seconds. For example, TIME_TO_SEC('01:00:00') returns 3600.
  • SECOND(): This function returns the second of a time or datetime value. For example, SECOND('2023-01-15 10:02:34') returns 34.
  • MINUTE(): This function returns the minute of a time or datetime value. For example, MINUTE('2023-01-15 10:02:34') returns 2.
  • HOUR(): This function returns the hour of a time or datetime value. For example, HOUR('2023-01-15 10:02:34') returns 10.
  • TIME_FORMAT(): This function returns the time value formatted according to a specified format. For example, TIME_FORMAT('10:02:34', '%h:%i %p') returns ‘10:02 AM’.

Conclusion

In this article, we learned how to use the MySQL SEC_TO_TIME() function, which converts a number of seconds to a time value. We also saw some examples of how to use this function in different situations, and explored some related functions that can be helpful for working with seconds and time values.