In this tutorial, we will learn about the MySQL TIME_TO_SEC() function. Suppose you have been given a bunch of time values from a stopwatch in a MySQL table and you have to convert them to their equivalent value in seconds. How would you do that? One way is to convert the time values manually to the seconds equivalent and then insert them into the table using the MySQL INSERT statement. The other way is to use the MySQL TIME_TO_SEC() function.
The MySQL TIME_TO_SEC() function is used to convert a time value in format HH:MM:SS to its seconds equivalent. Let us take a look at the syntax and then dive into some examples.
Recommended read – MySQL SEC_TO_TIME()
Syntax of MySQL TIME_TO_SEC()
TIME_TO_SEC(time)Code language: SQL (Structured Query Language) (sql)Where ‘time’ is a value that we need to convert to seconds.
Examples of MySQL TIME_TO_SEC()
Let us kick things off with some basic examples. I have two time values – ‘00:02:00’ and ‘01:00:00’. Let us convert these to seconds. We will use the SELECT statement and an alias called ‘Seconds’ that will make our output readable. The queries are –
SELECT TIME_TO_SEC('00:02:00') AS Seconds;
SELECT TIME_TO_SEC('01:00:00') AS Seconds;Code language: SQL (Structured Query Language) (sql)And the output is –
MySQL TIME_TO_SEC() With Large Values
Let us take some bigger time values than what we saw earlier. Consider the below example queries.
SELECT TIME_TO_SEC('12:45:20') AS Seconds;
SELECT TIME_TO_SEC('20:15:45') AS Seconds;Code language: SQL (Structured Query Language) (sql)And the output is –
MySQL TIME_TO_SEC() With Wrong Time Values
What if we pass invalid time values to the TIME_TO_SEC() function? An invalid time value is one in which the minute and second values are greater than 59. Let us see how the MySQL TIME_TO_SEC() reacts to such values using the below examples.
SELECT TIME_TO_SEC('05:32:75') AS Seconds;
SELECT TIME_TO_SEC('05:82:12') AS Seconds;Code language: SQL (Structured Query Language) (sql)And the output is –
TIME_TO_SEC() returns NULL if we pass an incorrect time value to it.
TIME_TO_SEC() With CURTIME()
We can pass functions like CURTIME() which return a time value to the TIME_TO_SEC() function. Let us see an example of this.
SELECT TIME_TO_SEC(CURTIME()) AS Seconds;Code language: SQL (Structured Query Language) (sql)And the output is –
TIME_TO_SEC() With Fractional Seconds Precision Time Value
We can pass a fractional seconds precision time value to the TIME_TO_SEC() function. Let us see an example of this. Consider the below queries.
SELECT TIME_TO_SEC('01:00:00.002') AS Seconds;
SELECT TIME_TO_SEC('00:01:00.12562') AS Seconds;Code language: SQL (Structured Query Language) (sql)And the output is –
As you can see, we get a seconds value with fractional seconds precision as well.
Limitation of the Time Data Type
One thing you should know while working with the TIME_TO_SEC() function is that the time datatype has a limitation. The time data type is limited to a range from -838:59:59 to 838:59:59. So, if you pass a huge seconds value, enough to cross this range then you will get a warning. Let us see this behaviour using the two below queries.
SELECT TIME_TO_SEC('1005:02:44') AS Seconds;
SELECT TIME_TO_SEC('935:02:44') AS Seconds;Code language: SQL (Structured Query Language) (sql)And the output is –
As you can see, you get a warning in the result. However, you also get a seconds value. Notice that this value is the same for both our queries. This can be quite misleading. If we pass a time value that is large enough to break out of the range I specified above, then MySQL stops counting at the seconds value after 838:59:59 and returns the seconds value of the time 838:59:59 even if the result would’ve been larger than that. Let us see the seconds value of the time – ‘838:59:59’.
SELECT TIME_TO_SEC('838:59:59') AS Seconds; Code language: SQL (Structured Query Language) (sql)And the output is –
Working With Tables
Finally, let us see an example with tables. Consider the below Shops table.
The ‘OpeningTime’ column specifies the time at which the Shops opens every day. Now we have inferred so far that TIME_TO_SEC() counts second values from the time 00:00:00. Let us use TIME_TO_SEC() to count the number of seconds between 12AM midnight and the opening time of each shop. The query for it is –
SELECT ID, Name, TIME_TO_SEC(OpeningTime) AS 'Seconds Since 12AM' FROM Shops;Code language: SQL (Structured Query Language) (sql)And the output is –
Conclusion
TIME_TO_SEC() is a very useful function that lets you convert time to a seconds value. I would encourage you to practice more examples of this function.
References
- MySQL Official Documentation on the
TIME_TO_SEC()function.