MySQL - SUBTIME() 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 SUBTIME() function is used to subtract the specified time interval to a date time or, time value.

Syntax

Following is the syntax of the above function –

SUBTIME(expr1, expr2)

Where,

• expr1 is the expression representing the datetime or time.

• expr2 is the expression representing the time interval to be added.

Example 1

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

mysql> SELECT SUBTIME('10:40:32.88558', '06:04:01.222222');
+----------------------------------------------+
| SUBTIME('10:40:32.88558', '06:04:01.222222') |
+----------------------------------------------+
| 04:36:31.663358                              |
+----------------------------------------------+
1 row in set (0.03 sec)

Example 2

Following is another example of this function –

mysql> SELECT SUBTIME('22:23:15.99999', '12:25:11.11111');
+---------------------------------------------+
| SUBTIME('22:23:15.99999', '12:25:11.11111') |
+---------------------------------------------+
| 09:58:04.888880                             |
+---------------------------------------------+
1 row in set (0.00 sec)

Example 3

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

mysql> SELECT SUBTIME('2018-05-23 15:40:32.88558', '06:04:01.222222');
+---------------------------------------------------------+
| SUBTIME('2018-05-23 15:40:32.88558', '06:04:01.222222') |
+---------------------------------------------------------+
| 2018-05-23 09:36:31.663358                              |
+---------------------------------------------------------+
1 row in set (0.00 sec)

Example 4

In the following example we are passing the result of the CURTIME() function as the time interval —

mysql> SELECT SUBTIME('2018-05-23 05:40:32.88558', CURTIME());
+-------------------------------------------------+
| SUBTIME('2018-05-23 05:40:32.88558', CURTIME()) |
+-------------------------------------------------+
| 2018-05-22 13:35:54.885580                      |
+-------------------------------------------------+
1 row in set (0.00 sec)

Example 5

We can also pass negative values as arguments to this function –

mysql> SELECT SUBTIME('2018-05-23 05:40:32.88558', '-06:04:01.222222');
+----------------------------------------------------------+
| SUBTIME('2018-05-23 05:40:32.88558', '-06:04:01.222222') |
+----------------------------------------------------------+
| 2018-05-23 11:44:34.107802                               |
+----------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT ADDTIME('06:23:15.99999', '-02:25:11.11111');
+----------------------------------------------+
| ADDTIME('06:23:15.99999', '-02:25:11.11111') |
+----------------------------------------------+
| 03:58:04.888880                              |
+----------------------------------------------+
1 row in set (0.00 sec)

Example 6

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 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');

Following query subtracts time interval from the values in the column named DispatchTime —

mysql> SELECT ProductName, CustomerName, DispatchDate, DispatchTime, Price, SUBTIME(DispatchTime,'12:45:50') FROM Sales;
+-------------+--------------+--------------+--------------+-------+----------------------------------+
| ProductName | CustomerName | DispatchDate | DispatchTime | Price | SUBTIME(DispatchTime,'12:45:50') |
+-------------+--------------+--------------+--------------+-------+----------------------------------+
| Key-Board   | Raja         | 2019-09-01   | 11:00:00     | 7000  | -01:45:50                        |
| Earphones   | Roja         | 2019-05-01   | 11:00:00     | 2000  | -01:45:50                        |
| Mouse       | Puja         | 2019-03-01   | 10:59:59     | 3000  | -01:45:51                        |
| Mobile      | Vanaja       | 2019-03-01   | 10:10:52     | 9000  | -02:34:58                        |
| Headset     | Jalaja       | 2019-04-06   | 11:08:59     | 6000  | -01:36:51                        |
+-------------+--------------+--------------+--------------+-------+----------------------------------+
5 rows in set (0.03 sec)

Example 7

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

mysql> CREATE TABLE dispatches_data(
	ProductName VARCHAR(255),
	CustomerName VARCHAR(255),
	DispatchTimeStamp timestamp,
	Price INT,
	Location VARCHAR(255)
);
insert into dispatches_data values('Key-Board', 'Raja', TIMESTAMP('2019-05-04', '15:02:45'), 7000, 'Hyderabad');
insert into dispatches_data values('Earphones', 'Roja', TIMESTAMP('2019-06-26', '14:13:12'), 2000, 'Vishakhapatnam');
insert into dispatches_data values('Mouse', 'Puja', TIMESTAMP('2019-12-07', '07:50:37'), 3000, 'Vijayawada');
insert into dispatches_data values('Mobile', 'Vanaja' , TIMESTAMP ('2018-03-21', '16:00:45'), 9000, 'Chennai');
insert into dispatches_data values('Headset', 'Jalaja' , TIMESTAMP('2018-12-30', '10:49:27'), 6000, 'Goa');

Following query subtracts time interval from the column named DispatchTimeStamp —

mysql> SELECT ProductName, CustomerName, DispatchTimeStamp, Price, SUBTIME(DispatchTimeStamp, '08:25:46') FROM dispatches_data;
+-------------+--------------+---------------------+-------+----------------------------------------+
| ProductName | CustomerName | DispatchTimeStamp   | Price | SUBTIME(DispatchTimeStamp, '08:25:46') |
+-------------+--------------+---------------------+-------+----------------------------------------+
| Key-Board   | Raja         | 2019-05-04 15:02:45 | 7000  | 2019-05-04 06:36:59                    |
| Earphones   | Roja         | 2019-06-26 14:13:12 | 2000  | 2019-06-26 05:47:26                    |
| Mouse       | Puja         | 2019-12-07 07:50:37 | 3000  | 2019-12-06 23:24:51                    |
| Mobile      | Vanaja       | 2018-03-21 16:00:45 | 9000  | 2018-03-21 07:34:59                    |
| Headset     | Jalaja       | 2018-12-30 10:49:27 | 6000  | 2018-12-30 02:23:41                    |
+-------------+--------------+---------------------+-------+----------------------------------------+
5 rows in set (0.00 sec)

Example 8

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

Following query subtracts time interval from the SubscriptionTimeStamp values of all the records –

mysql> SELECT SubscriberName, PackageName, SubscriptionTimeStamp, SUBTIME(SubscriptionTimeStamp, '10:05:20') FROM SubscriberDetails;
+----------------+-------------+-----------------------+--------------------------------------------+
| SubscriberName | PackageName | SubscriptionTimeStamp | SUBTIME(SubscriptionTimeStamp, '10:05:20') |
+----------------+-------------+-----------------------+--------------------------------------------+
| Ram            | Premium     | 2020-10-21 20:53:49   | 2020-10-21 10:48:29                        |
| Rahman         | Basic       | 2020-11-26 10:13:19   | 2020-11-26 00:07:59                        |
| Robert         | Moderate    | 2021-03-07 05:43:20   | 2021-03-06 19:38:00                        |
| Radha          | Basic       | 2021-02-21 16:36:39   | 2021-02-21 06:31:19                        |
| Rajiya         | Premium     | 2021-01-30 12:45:45   | 2021-01-30 02:40:25                        |
+----------------+-------------+-----------------------+--------------------------------------------+
5 rows in set (0.00 sec)