When using MySQL, you can use the TIME_TO_SEC()
function to return the number of seconds in a time value. Specifically, this function returns the time argument, converted to seconds.
This function is not to be confused with the TO_SECONDS()
function, which, given a date or datetime argument, returns the number of seconds since year 0.
Here’s how TIME_TO_SEC()
works.
Syntax
The syntax goes like this:
TIME_TO_SEC(time)
Where time
is the time value you want converted to seconds.
Example 1 – Basic Example
Here’s an example to demonstrate.
SELECT TIME_TO_SEC('00:01:00');
Result:
+-------------------------+ | TIME_TO_SEC('00:01:00') | +-------------------------+ | 60 | +-------------------------+
Example 2 – Larger Value
And here’s what it looks like when we use a larger time value:
SELECT TIME_TO_SEC('01:00:00');
Result:
+-------------------------+ | TIME_TO_SEC('01:00:00') | +-------------------------+ | 3600 | +-------------------------+
Example 3 – Current Time
You can pass the CURTIME()
function as an argument to return the number of seconds in the current time.
SELECT CURTIME() AS 'Current Time', TIME_TO_SEC(CURTIME()) AS 'Seconds';
Result:
+--------------+---------+ | Current Time | Seconds | +--------------+---------+ | 09:04:47 | 32687 | +--------------+---------+
Here’s the same example, but using the CURRENT_TIME()
function (which is a synonym for CURTIME()
).
SELECT CURRENT_TIME() AS 'Current Time', TIME_TO_SEC(CURRENT_TIME()) AS 'Seconds';
Result:
+--------------+---------+ | Current Time | Seconds | +--------------+---------+ | 09:05:23 | 32723 | +--------------+---------+
Example 4 – Elapsed Time
The time data type isn’t limited to just time of the day. It can also be used to represent elapsed time.
Here’s an example of getting the number of seconds in 400 hours.
SELECT TIME_TO_SEC('400:00:00');
Result:
+--------------------------+ | TIME_TO_SEC('400:00:00') | +--------------------------+ | 1440000 | +--------------------------+