MySQL - SEC_TO_TIME() Function

---

The DATE, DATETIME and TIMESTAMP datatypes in MySQL are used to store the date, date and time, time stamp values respectively. Where a time stamp is a numerical value representing the number of milliseconds from '1970-01-01 00:00:01' UTC (epoch) to the specified time. MySQL provides a set of functions to manipulate these values.

The MYSQL SEC_TO_TIME() function accepts a numerical value representing seconds as an argument, converts it into TIME value (hours, minutes and seconds) and returns the result as a numerical value.

Syntax

Following is the syntax of the above function –

SEC_TO_TIME(time);

Example 1

Following example demonstrates the usage of the SEC_TO_TIME() function –

mysql> SELECT SEC_TO_TIME(71122);
+--------------------+
| SEC_TO_TIME(71122) |
+--------------------+
|           19:45:22 |
+--------------------+
1 row in set (0.00 sec)
mysql>

mysql> SELECT SEC_TO_TIME(28529);
+--------------------+
| SEC_TO_TIME(28529) |
+--------------------+
|           07:55:29 |
+--------------------+
1 row in set (0.00 sec)

Example 2

Following is another example of this function –

mysql> SELECT SEC_TO_TIME(41400);
+--------------------+
| SEC_TO_TIME(41400) |
+--------------------+
|           11:30:00 |
+--------------------+
1 row in set (0.00 sec)
mysql> SELECT SEC_TO_TIME(57022);
+--------------------+
| SEC_TO_TIME(57022) |
+--------------------+
|           15:50:22 |
+--------------------+
1 row in set (0.00 sec)

Example 3

mysql> SELECT SEC_TO_TIME(45358);
+--------------------+
| SEC_TO_TIME(45358) |
+--------------------+
|           12:35:58 |
+--------------------+
1 row in set (0.00 sec)
mysql> SELECT SEC_TO_TIME(74432);
+--------------------+
| SEC_TO_TIME(74432) |
+--------------------+
|           20:40:32 |
+--------------------+
1 row in set (0.00 sec)

Example 4

mysql> SELECT SEC_TO_TIME(54334);
+--------------------+
| SEC_TO_TIME(54334) |
+--------------------+
|           15:05:34 |
+--------------------+
1 row in set (0.00 sec)

Example 5

Let us create another table with name Sales in MySQL database using CREATE statement as follows –

mysql> CREATE TABLE sales(
	ID INT,
	ProductName VARCHAR(255),
	CustomerName VARCHAR(255),
	DispatchDate date,
	DispatchTime INT,
	Price INT,
	Location VARCHAR(255)
);
Query OK, 0 rows affected (2.22 sec)

Now, we will insert 5 records in Sales table using INSERT statements:

insert into sales values (1, 'Key-Board', 'Raja', DATE('2019-09-01'), 39600, 7000, 'Hyderabad');
insert into sales values (2, 'Earphones', 'Roja', DATE('2019-05-01'), 39600, 2000, 'Vishakhapatnam');
insert into sales values (3, 'Mouse', 'Puja', DATE('2019-03-01'), 39599, 3000, 'Vijayawada');
insert into sales values (4, 'Mobile', 'Vanaja', DATE('2019-03-01'), 36652, 9000, 'Chennai');
insert into sales values (5, 'Headset', 'Jalaja', DATE('2019-04-06'), 40139, 6000, 'Goa');

In the following query we are passing the column (name) DispatchTime as an argument of this function –

mysql> SELECT ProductName, CustomerName, DispatchDate, DispatchTime, Price, SEC_TO_TIME(DispatchTime) as Time FROM sales;
+-------------+--------------+--------------+--------------+-------+----------+
| ProductName | CustomerName | DispatchDate | DispatchTime | Price |     Time |
+-------------+--------------+--------------+--------------+-------+----------+
| Key-Board   | Raja         | 2019-09-01   | 39600        | 7000  | 11:00:00 |
| Earphones   | Roja         | 2019-05-01   | 39600        | 2000  | 11:00:00 |
| Mouse       | Puja         | 2019-03-01   | 39599        | 3000  | 10:59:59 |
| Mobile      | Vanaja       | 2019-03-01   | 36652        | 9000  | 10:10:52 |
| Headset     | Jalaja       | 2019-04-06   | 40139        | 6000  | 11:08:59 |
+-------------+--------------+--------------+--------------+-------+----------+
5 rows in set (0.00 sec)

Example 6

Suppose we have created a table named SubscriberDetails with 5 records in it using the following queries –

mysql> CREATE TABLE SubscriberDetails (
	SubscriberName VARCHAR(255),
	PackageName VARCHAR(255),
	SubscriptionTimestamp INT
);
insert into SubscriberDetails values('Raja', 'Premium', 75229);
insert into SubscriberDetails values('Roja', 'Basic', 36799);
insert into SubscriberDetails values('Puja', 'Moderate', 20600);
insert into SubscriberDetails values('Vanaja', 'Basic', 59799);
insert into SubscriberDetails values('Jalaja', 'Premium', 45945);

We can also pass the seconds representing Timestamp values as an argument of this function.

mysql> SELECT SubscriberName, PackageName, SubscriptionTimeStamp, SEC_TO_TIME(SubscriptionTimestamp) AS Time FROM SubscriberDetails;
+----------------+-------------+-----------------------+----------+
| SubscriberName | PackageName | SubscriptionTimeStamp |     Time |
+----------------+-------------+-----------------------+----------+
| Raja           | Premium     | 75229                 | 20:53:49 |
| Roja           | Basic       | 36799                 | 10:13:19 |
| Puja           | Moderate    | 20600                 | 05:43:20 |
| Vanaja         | Basic       | 59799                 | 16:36:39 |
| Jalaja         | Premium     | 45945                 | 12:45:45 |
+----------------+-------------+-----------------------+----------+
5 rows in set (0.00 sec)