In this tutorial, we will learn about the MySQL SEC_TO_TIME() function. Suppose you are an athletics coach and you are timing the runs of your athletes who are running a race. Your stopwatch only counts in seconds (just consider you are using some ancient stopwatch). Now, you want to store these timing values in the format HH:MM:SS in a table.
How would you do that? One way is to convert the seconds manually to the above format and then insert them into the table using the INSERT statement. The other way is to use the MySQL SEC_TO_TIME() function.
The MySQL SEC_TO_TIME() function is used to return a time value in format HH:MM:SS from the specified number of seconds. In other words, it converts a value in seconds only to the format HH:MM:SS. Let us take a look at the syntax and then dive into some examples.
Syntax of MySQL SEC_TO_TIME() function
SEC_TO_TIME(seconds)Code language: SQL (Structured Query Language) (sql)Where, ‘seconds’ is the number of seconds. This value can be both – positive and negative.
Examples of MySQL SEC_TO_TIME() function
Let us start by looking at a couple of basic examples. Let us convert the following number of seconds to a time value – 1 and 156. We will use the SELECT statement and an alias called ‘Time’ to make our output readable. The query is –
SELECT SEC_TO_TIME(1) AS Time;
SELECT SEC_TO_TIME(156) AS Time;Code language: SQL (Structured Query Language) (sql)And the output is –
So, SEC_TO_TIME() starts counting the number of seconds from the time 00:00:00. Now, let us take a look at a couple of examples where we pass a larger seconds value. Consider the below queries –
SELECT SEC_TO_TIME(65536) AS Time;
SELECT SEC_TO_TIME(655360) AS Time;Code language: SQL (Structured Query Language) (sql)And the output is –
Limitations Of The Time Datatype
One thing you should know while working with the SEC_TO_TIME() 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 SEC_TO_TIME(6555555) AS Time;
SELECT SEC_TO_TIME(6555555000) AS Time;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 value. Notice that this value is the same for both our queries. This can be quite misleading. If we pass seconds large enough to break out of the range I specified above, then MySQL stops counting at the time value 838:59:59 even if the result would’ve been larger than that.
MySQL SEC_TO_TIME() With Negative Seconds Value
We can also pass a negative seconds value to the SEC_TO_TIME() function. MySQL SEC_TO_TIME() will then start counting backward and give you a negative time value. Let us see an example of this. Consider the below query.
SELECT SEC_TO_TIME(-156) AS Time; Code language: SQL (Structured Query Language) (sql)And the output is –
MySQL SEC_TO_TIME() In Numeric Context
Let us see an example of SEC_TO_TIME() in the numeric context. Consider the below query.
SELECT SEC_TO_TIME(450), SEC_TO_TIME(450) + 0;Code language: SQL (Structured Query Language) (sql)And the output is –
Working With Tables
Let us see an example with tables. Consider the below Runner table.
C1 and C2 are the checkpoints in a race. Consider you are an athletics coach and you are timing your athletes on when they cross the C1 and C2 checkpoints in a race. Let us write a query that will fill the C1 and C2 columns with a time value for the runners with the following seconds values –
- Jim Humber passes checkpoint C1 at 500 seconds and C2 at 2010 seconds.
- Denise Torvalds crosses checkpoint C1 at 650 seconds and C2 at 2510 seconds.
- Johanna Huxley crosses checkpoint C1 at 885 seconds and C2 at 3000 seconds.
We will use the UPDATE statement and the WHERE clause for this. Consider the below queries.
UPDATE Runner SET C1=SEC_TO_TIME(500), C2=SEC_TO_TIME(2010) WHERE rid=1;
UPDATE Runner SET C1=SEC_TO_TIME(650), C2=SEC_TO_TIME(2510) WHERE rid=2;
UPDATE Runner SET C1=SEC_TO_TIME(885), C2=SEC_TO_TIME(3000) WHERE rid=3;
SELECT * FROM Runner;Code language: SQL (Structured Query Language) (sql)And the output is –
Conclusion
MySQL SEC_TO_TIME() is a very useful function that lets you convert seconds to a time value. I would encourage you to practice more examples of this function.
References
- MySQL Official Documentation on the
SEC_TO_TIME()function.