MySQL SEC_TO_TIME() – Convert Seconds to a Time Values in MySQL

SEC TO TIME Function

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)

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;

And the output is –

Sec To Time Basic Example

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;

And the output is – 

Sec To Time Large Values

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;

And the output is – 

MySQL SEC_TO_TIME Limitation

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; 

And the output is – 

MySQL SEC_TO_TIME Negative

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;

And the output is – 

Sec To Time Numeric

Working With Tables

Let us see an example with tables. Consider the below Runner table.

Sec To Time Runner Table
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;

And the output is – 

Sec To Time Table Example1

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