MySQL - SUBTIME() Function



The MySQL SUBTIME() function is used to subtract the specified time interval to a date time or, time value. Unlike SUBDATE() function, this function takes date, datetime or time values as arguments instead of just date values only.

This MySQL function accepts two arguments: either datetime, or time values, subtracts the expression representing a time interval from the expression representing either date, datetime or time, and returns the final timestamp obtained. A NULL value is returned by this function if either of the two arguments is NULL.

Syntax

Following is the syntax of MySQL SUBTIME() function −

SUBTIME(expr1, expr2)

Parameters

This method accepts two parameters. The same is described below −

  • expr1 is the expression representing the datetime or time.

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

Return value

This function returns the result of subtracting the specified time expression from the given time. The return value is a new time value.

Example

In the following query, we are using the SUBTIME() function to subtract the specified time values −

SELECT SUBTIME('10:40:32', '06:04:01') As Result;

Output

This will produce the following result −

Result
04:36:31

Example

Here, we are passing DATETIME value as first parameter and TIME value as second parameter −

SELECT SUBTIME('2018-05-23 15:40:32', '24:04:01') As Result;

Output

Following is the output −

Result
2018-05-22 15:36:31

Example

In this example, we have created a table named ORDERS using the following CREATE TABLE query −

CREATE TABLE ORDERS (
   OID INT NOT NULL,
   DATE VARCHAR (20) NOT NULL,
   CUSTOMER_ID INT NOT NULL,
   AMOUNT DECIMAL (18, 2)
);

Now, insert the following records into the ORDERS table using the INSERT statement −

INSERT INTO ORDERS VALUES 
(102, '2009-10-08 00:00:00', 3, 3000.00),
(100, '2009-10-08 00:00:00', 3, 1500.00),
(101, '2009-11-20 00:00:00', 2, 1560.00),
(103, '2008-05-20 00:00:00', 4, 2060.00);

Execute the below query to fetch all the inserted records in the above-created table −

Select * From ORDERS;

Following is the ORDERS table −

OID DATE CUSTOMER_ID AMOUNT
102 2009-10-08 00:00:00 3 3000.00
100 2009-10-08 00:00:00 3 1500.00
101 2009-11-20 00:00:00 2 1560.00
103 2008-05-20 00:00:00 4 2060.00

Here, we are using the SUBTIME() function to subtract 24 hours from the values in the "DATE" column −

SELECT OID, DATE, SUBTIME(DATE, '24:00:00')
As Result From ORDERS;

Output

The output is displayed as follows −

OID DATE Result
102 2009-10-08 00:00:00 2009-10-07 00:00:00
100 2009-10-08 00:00:00 2009-10-07 00:00:00
101 2009-11-20 00:00:00 2009-11-19 00:00:00
103 2008-05-20 00:00:00 2008-05-19 00:00:00
Advertisements