MySQL - TIMEDIFF() Function

The MySQL TIMEDIFF() function accepts two time or, date-time values as parameters, calculates the difference between them (argument1-argument2) and returns the result. Both arguments of this function must be of same type (either time or date-time).

A NULL value is returned by this function if either of the two arguments is NULL.

Unlike the SUBTIME() function, that only subtracts the specified time interval from a time value, the MySQL TIMEDIFF() function is used to subtract the specified time interval from a datetime or, time value.

Syntax

Following is the syntax of MySQL TIMEDIFF() function −

TIMEDIFF(expr1, expr2)

Parameters

This method accepts two time expressions (datetime or time) for which you want to find the difference as a parameter.

Return value

This function returns the time difference between expr1 and expr2 as a time value. The result is in the format 'hh:mm:ss'.

Example

In the following query, we are using the MySQL TIMEDIFF() function to calculate the time difference between the given time values −

SELECT TIMEDIFF('20:10;00', '15:10:00') As Result;

Output

This will produce the following result −

Result
05:00:00

Example

Here, we are passing DATETIME values as an argument to function to calculate the time difference between them −

SELECT TIMEDIFF('2018-05-23 18:00:00', '2018-05-23 12:00:00')
As Result;

Output

Following is the output −

Result
06:00:00

Example

In the following example, we are passing the result of CURTIME() as an argument to the TIMEDIFF() function −

SELECT TIMEDIFF('20:00:00', CURTIME()) As Result;

Output

Following is the output −

Result
08:14:43

Example

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

CREATE TABLE SUBSCRIBERS(
   NAME varchar(255),
   PACKAGE varchar(255),
   SubscriptionDate date,
   SubscriptionTime time
);

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

INSERT INTO SUBSCRIBERS VALUES
('Raja', 'Premium', Date('2020-10-21'), Time('04:00:00')),
('Roja', 'Basic', Date('2020-11-26'), Time('10:15:00')),
('Puja', 'Moderate', Date('2021-03-07'), Time('05:40:30')),
('Vanaja', 'Basic', Date('2021-02-21'), Time('02:30:00')),
('Jalaja', 'Premium', Date('2021-01-30'), Time('05:45:15'));

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

Select * From SUBSCRIBERS;

Following is the SUBSCRIBERS table −

NAME PACKAGE SubscriptionDate SubscriptionTime
Raja Premium 2020-10-21 04:00:00
Roja Basic 2020-11-26 10:15:00
Puja Moderate 2021-03-07 05:40:30
Vanaja Basic 2021-02-21 02:30:00
Jalaja Premium 2021-01-30 05:45:15

Now, we use the MySQL TIMEDIFF() function to calculate the time difference between the current time and the "SubscriptionTime" column −

SELECT NAME, PACKAGE, TIMEDIFF(CURTIME(), SubscriptionTime) 
As RemainingTime FROM SUBSCRIBERS;

Output

The output is displayed as follows −

NAME PACKAGE SubscriptionTime
Raja Premium 08:15:58
Roja Basic 02:00:58
Puja Moderate 06:35:28
Vanaja Basic 09:45:58
Jalaja Premium 06:30:43
Advertisements