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 | +-------------------+-----------------------+