MySQL - TIMESTAMPDIFF() Function

The MySQL TIMESTAMPDIFF() function is used to calculate the difference between two datetime or, date expressions.

This function accepts two datetime or date expressions as parameter values, calculates the difference between them and returns the result. One of the arguments can be date and the other a datetime expression.

This function returns NULL if any of its arguments is passed as NULL.

Syntax

Following is the syntax of MySQL TIMESTAMPDIFF() function −

TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)

Parameters

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

  • unit is the interval type represented by the expr value which can be DAY, WEEK, MONTH, QUARTER, YEAR, HOUR, MINUTE, SECOND, MICROSECOND.

  • datetime_expr1 and datetime_expr2 are the date or date-time expressions.

Return value

This function returns the difference between the two datetime expressions in terms of the specified unit.

Example

In the following example, we are using the MySQL TIMESTAMPDIFF() function to calculate the DAY difference between the specified two date values −

SELECT TIMESTAMPDIFF(DAY, '1898-03-22', '2019-05-02') As Result;

Output

This will produce the following result −

Result
44235

Example

Here, we are calculating the QUARTER difference between two date values −

SELECT TIMESTAMPDIFF(QUARTER, '2022-11-29', '2023-11-29')
As Result;

Output

Following is the output −

Result
4

Example

In the following query, we are calculating HOUR difference between two datetime values −

SELECT TIMESTAMPDIFF(HOUR,'2023-05-12 12:00:00','2023-05-13 12:00:00')
As Result;

Output

Following is the output −

Result
24

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 MySQL TIMESTAMPDIFF() function to calculate the difference in years between the "DATE" column and the current timestamp −

SELECT OID, DATE, TIMESTAMPDIFF(YEAR , DATE, CURRENT_TIMESTAMP())
As Result FROM ORDERS;

Output

The output is displayed as follows −

OID DATE Result
102 2009-10-08 00:00:00 14
100 2009-10-08 00:00:00 14
101 2009-11-20 00:00:00 13
103 2008-05-20 00:00:00 15
Advertisements