Open In App

TIMESTAMPDIFF() function in MYSQL

Improve
Improve
Like Article
Like
Save
Share
Report

TIMESTAMPDIFF() :

This function in MySQL is used to return a value after subtracting a DateTime expression from another.

Syntax :

TIMESTAMPDIFF(unit,expr1,expr2)

Parameters : 

It will accept three parameters.

  • unit – 
    It denotes the unit for the result. It can be one of the following.
    MICROSECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR
     
  • expr1 – 
    First date or DateTime expressions.
     
  • expr2 – 
    Second date or DateTime expressions.

Returns :

It returns the DateTime expressions after subtraction.

Example 1 :

Getting the differences between two specified time values where the time is specified in the format of YYYY-MM-DD HH-MM-SS. Here expr2 is greater than expr1, so the return value is positive.

SELECT TIMESTAMPDIFF(SECOND, '2010-01-01 10:10:20', '2010-01-01 10:45:59') AS SECONDDIFFERENCE;

Output :

SECONDDIFFERENCE
2139

Example 2:

Getting the differences between two specified time values where the time is specified in the format of YYYY-MM-DD HH-MM-SS. Here expr2 is lesser than expr1, so the return value is negative.

SELECT TIMESTAMPDIFF(SECOND, '2010-01-01 10:10:20', '2010-01-01 09:45:59') AS SECONDDIFFERENCE;

Output:

SECONDDIFFERENCE
-1461

Example 3:

Getting the differences between two specified date values in the month when the date is specified in the format of YYYY-MM-DD.

SELECT TIMESTAMPDIFF(MONTH, '2019-08-01', '2020-11-01') AS MONTHDIFFERENCE;

Output:

MONTHDIFFERENCE
15

Example 4:

Calculating Total Work experience of an Employee using the TIMESTAMPDIFF function. 

Creating an Employee table –

CREATE TABLE Employee(
    id INT AUTO_INCREMENT PRIMARY KEY,
    Full_Name VARCHAR(50) NOT NULL,
    Joining_Date DATE NOT NULL
);

Inserting values into the table –

INSERT INTO Employee(Full_Name , Joining_Date )
VALUES('Riya Jana', '2000-01-01'),
      ('Sayan Ghosh', '2005-09-26'),
      ('Rinki Sharma', '2014-08-12'),
      ('Aniket Singh', '2019-11-05');

Now, we will use the TIMESTAMPDIFF to calculate the work experience of each employee in the year.

SELECT 
    id,
    Full_Name,
    Joining_Date ,
    TIMESTAMPDIFF(YEAR, Joining_Date,'2020-11-26') AS WorkExperience
FROM
    Employee ;

Output :

ID FULL_NAME JOINING_DATE WORKEXPERIENCE
1  Riya Jana  2000-01-01 20
2  Sayan Ghosh 2005-09-26 15
3 Rinki Sharma 2014-08-12 6
4  Aniket Singh  2019-11-05 1

Last Updated : 30 Dec, 2020
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads