SEC_TO_TIME() Examples – MySQL

Posted on by Ian

When using MySQL, you can use the SEC_TO_TIME() function to build a time value based on a given number of seconds. Basically, you provide the number of seconds as an argument, and it will convert that to a time value.

Here’s how it works.

Syntax

The syntax goes like this:

SEC_TO_TIME(seconds)

Where seconds is the number of seconds you want converted to a time value.

Example 1 – Basic Usage

Here’s an example to demonstrate.

SELECT SEC_TO_TIME(65);

Result:

+-----------------+
| SEC_TO_TIME(65) |
+-----------------+
| 00:01:05        |
+-----------------+

Example 2 – A Larger Value

Here’s an example with a slightly larger value.

SELECT SEC_TO_TIME(6555);

Result:

+-------------------+
| SEC_TO_TIME(6555) |
+-------------------+
| 01:49:15          |
+-------------------+

Here’s one with an even larger value again.

SELECT SEC_TO_TIME(655555);

Result:

+---------------------+
| SEC_TO_TIME(655555) |
+---------------------+
| 182:05:55           |
+---------------------+

So the time value is not limited to 24 hours. This is because it’s not limited to representing just the time of the day. It could also represent elapsed time or a time interval between two events.

Example 3 – Limitations on the time Data Type

The time data type is limited to a range from -838:59:59 to 838:59:59. If the result falls outside that range, you’ll get a warning.

SELECT SEC_TO_TIME(6555555);

Result:

+----------------------+
| SEC_TO_TIME(6555555) |
+----------------------+
| 838:59:59            |
+----------------------+
1 row in set, 1 warning (0.00 sec)

Be careful though. The time value shown could be misleading, as it will stop at 838:59:59 even if the result would’ve been larger than that.

Here’s another example using yet a larger value again.

SELECT SEC_TO_TIME(65555550000);

Result:

+--------------------------+
| SEC_TO_TIME(65555550000) |
+--------------------------+
| 838:59:59                |
+--------------------------+
1 row in set, 1 warning (0.00 sec)

Notice that we get the same result as in the previous example, even though the seconds argument is much larger in this one.

Example 4 – Numeric Context

Here’s an example of using SEC_TO_TIME() in a numeric context. We do this by adding a value (in this case 0) to the statement.

SELECT 
    SEC_TO_TIME(6555),
    SEC_TO_TIME(6555) + 0;

Result:

+-------------------+-----------------------+
| SEC_TO_TIME(6555) | SEC_TO_TIME(6555) + 0 |
+-------------------+-----------------------+
| 01:49:15          |                 14915 |
+-------------------+-----------------------+