TIMESTAMPDIFF() function in MYSQL
Last Updated :
30 Dec, 2020
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 |
Similar Reads
UNIX_TIMESTAMP() function in MySQL
UNIX_TIMESTAMP() : This function in MySQL helps to return a Unix timestamp. We can define a Unix timestamp as the number of seconds that have passed since â1970-01-01 00:00:00âUTC. Even if you pass the current date/time or another specified date/time, the function will return a Unix timestamp based
2 min read
UTC_TIMESTAMP() function in MySQL
UTC_TIMESTAMP() function in MySQL is used to check current Coordinated Universal Time (UTC) date and time value. It returns the current UTC date and time value in YYYY-MM-DD HH:MM:SS or YYYYMMDDHHMMSS.uuu format, depending on whether the function is used in string or numeric context. Syntax : UTC_TI
2 min read
TIME() Function in MySQL
The TIME() function in MySQL is used to extract the time portion from a date or datetime expression, returning the time in the format 'HH:MM'. This function is particularly useful when working with time components in databases, such as scheduling or logging systems. In this article, We will learn ab
4 min read
CURRENT_TIMESTAMP() function in MySQL
CURRENT_TIMESTAMP() function in MySQL is used to check the current date and time value. In MySQL function, CURRENT_TIMESTAMP is needed when you need to keep a record of the exact time of delivery which is helpful in improving the services and functionalities of any organization. The format of this f
2 min read
UTC_TIME() function in MySQL
UTC_TIME() function in MySQL is used to check current Coordinated Universal Time (UTC) time value. It returns the UTC time value in 'HH:MM:SS' or HHMMSS format, depending on whether the function is used in string or numeric context. Syntax : UTC_TIME OR UTC_TIME() Parameter : This method does not ac
2 min read
STD() function in MySQL
With the help of STD() function we can calculate population Standard deviation of an expression in MySQL. But, if there are no matching rows in the given expression it returns Null. Syntax : STD(expr); Parameter : This method accepts only one parameter. expr : Input expression from which we want to
3 min read
STRCMP() Function in MySQL
STRCMP() function in MySQL is used to compare two strings. If both of the strings are same then it returns 0, if the first argument is smaller than the second according to the defined order it returns -1 and it returns 1 when the second one is smaller the first one. Syntax : STRCMP(Str1, Str2) Param
3 min read
TRIM() Function in MySQL
TRIM() function in MySQL is used to clean up data. It is also used to remove the unwanted leading and trailing characters in a string. Syntax : TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str) Parameter : This method accepts three-parameter as mentioned above and described below : BOTH | LEADIN
2 min read
RTRIM() Function in MySQL
RTRIM() : It is the function in MySQL that is used to remove trailing spaces from a string. Syntax : RTRIM(str) Parameter : RTRIM() function accepts one parameter as mentioned above and described below. str âThe string from which we want to remove trailing spaces. Returns : It returns a string after
3 min read
SEC_TO_TIME() Function in MySQL
SEC_TO_TIME() function : This function in MySQL is used to return a time value based on the specified seconds value. Here the returned time value will be in the format of HH:MM:SS. For example, if the specified second value is 63, this function will return "00:01:03". Syntax : SEC_TO_TIME(seconds) P
1 min read