TIMESTAMPDIFF() function in MYSQL
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 :
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:
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:
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
Share your thoughts in the comments
Please Login to comment...