MySQL - TIME_TO_SEC() 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 TIME_TO_SEC() function accepts a time value as an argument, converts it into seconds and returns the result as a numerical value.

Syntax

Following is the syntax of the above function –

TIME_TO_SEC(time);

Example 1

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

mysql> SELECT TIME_TO_SEC('19:45:22');
+-------------------------+
| TIME_TO_SEC('19:45:22') |
+-------------------------+
| 71122                   |
+-------------------------+
1 row in set (0.08 sec)
mysql> SELECT TIME_TO_SEC('07:55:29');
+-------------------------+
| TIME_TO_SEC('07:55:29') |
+-------------------------+
| 28529                   |
+-------------------------+
1 row in set (0.00 sec)

Example 2

Following is another example of this function –

mysql> SELECT TIME_TO_SEC('11:30:00');
+-------------------------+
| TIME_TO_SEC('11:30:00') |
+-------------------------+
| 41400                   |
+-------------------------+
1 row in set (0.00 sec)

mysql> SELECT TIME_TO_SEC('15:50:22');
+-------------------------+
| TIME_TO_SEC('15:50:22') |
+-------------------------+
| 57022                   |
+-------------------------+
1 row in set (0.00 sec)

Example 3

In the following example we are passing DATETIME value for date –

mysql> SELECT TIME_TO_SEC('1987-3-12 12:35:58');
+-----------------------------------+
| TIME_TO_SEC('1987-3-12 12:35:58') |
+-----------------------------------+
|                           45358   |
+-----------------------------------+
1 row in set (0.00 sec)

mysql> SELECT TIME_TO_SEC('2018-05-23 20:40:32');
+------------------------------------+
| TIME_TO_SEC('2018-05-23 20:40:32') |
+------------------------------------+
|                              74432 |
+------------------------------------+
1 row in set (0.00 sec)

Example 4

Following query converts the current time to days –

mysql> SELECT TIME_TO_SEC(CURTIME());
+------------------------+
| TIME_TO_SEC(CURTIME()) |
+------------------------+
|                  54334 |
+------------------------+
1 row in set (0.00 sec)

Example 5

We can also pass current timestamp values as arguments to this function –

mysql> SELECT TIME_TO_SEC(CURRENT_TIMESTAMP());
+----------------------------------+
| TIME_TO_SEC(CURRENT_TIMESTAMP()) |
+----------------------------------+
|                            54351 |
+----------------------------------+
1 row in set (0.00 sec)

Example 6

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

mysql> CREATE TABLE sales(
	ID INT,
	ProductName VARCHAR(255),
	CustomerName VARCHAR(255),
	DispatchDate date,
	DispatchTime time,
	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'), TIME('11:00:00'), 7000, 'Hyderabad');
insert into sales values (2, 'Earphones', 'Roja', DATE('2019-05-01'), TIME('11:00:00'), 2000, 'Vishakhapatnam');
insert into sales values (3, 'Mouse', 'Puja', DATE('2019-03-01'), TIME('10:59:59'), 3000, 'Vijayawada');
insert into sales values (4, 'Mobile', 'Vanaja', DATE('2019-03-01'), TIME('10:10:52'), 9000, 'Chennai');
insert into sales values (5, 'Headset', 'Jalaja', DATE('2019-04-06'), TIME('11:08:59'), 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, TIME_TO_SEC(DispatchTime) as Seconds FROM sales;
+-------------+--------------+--------------+--------------+-------+---------+
| ProductName | CustomerName | DispatchDate | DispatchTime | Price | Seconds |
+-------------+--------------+--------------+--------------+-------+---------+
| Key-Board   | Raja         | 2019-09-01   | 11:00:00     | 7000  | 39600   |
| Earphones   | Roja         | 2019-05-01   | 11:00:00     | 2000  | 39600   |
| Mouse       | Puja         | 2019-03-01   | 10:59:59     | 3000  | 39599   |
| Mobile      | Vanaja       | 2019-03-01   | 10:10:52     | 9000  | 36652   |
| Headset     | Jalaja       | 2019-04-06   | 11:08:59     | 6000  | 40139   |
+-------------+--------------+--------------+--------------+-------+---------+
5 rows in set (0.00 sec)

Example 7

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 timestamp
);
insert into SubscriberDetails values('Raja', 'Premium', TimeStamp('2020-10-21 20:53:49'));
insert into SubscriberDetails values('Roja', 'Basic', TimeStamp('2020-11-26 10:13:19'));
insert into SubscriberDetails values('Puja', 'Moderate', TimeStamp('2021-03-07 05:43:20'));
insert into SubscriberDetails values('Vanaja', 'Basic', TimeStamp('2021-02-21 16:36:39'));
insert into SubscriberDetails values('Jalaja', 'Premium', TimeStamp('2021-01-30 12:45:45'));

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

mysql> SELECT SubscriberName, PackageName, SubscriptionTimeStamp, TIME_TO_SEC(SubscriptionTimeStamp) AS Seconds FROM SubscriberDetails;
+----------------+-------------+-----------------------+---------+
| SubscriberName | PackageName | SubscriptionTimeStamp | Seconds |
+----------------+-------------+-----------------------+---------+
| Raja           | Premium     | 2020-10-21 20:53:49   | 75229   |
| Roja           | Basic       | 2020-11-26 10:13:19   | 36799   |
| Puja           | Moderate    | 2021-03-07 05:43:20   | 20600   |
| Vanaja         | Basic       | 2021-02-21 16:36:39   | 59799   |
| Jalaja         | Premium     | 2021-01-30 12:45:45   | 45945   |
+----------------+-------------+-----------------------+---------+
5 rows in set (0.10 sec)