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 |