![]() For this purpose, we will create a table named WorkTime. In this tutorial, you have learned how to use the MySQL TIMEDIFF function to calculate the difference between two TIME or DATETIME values. How to get the number of minutes worked by day of some employees using SQL subtract dates In this example, we will calculate the total minutes worked by 2 customers. ![]() The the following shows the difference between two DATETIME values in hour: + -+ To fix this problem, you need to use the TIMESTAMPDIFF function as follows: SELECT TIMESTAMPDIFF( So the result should be 1416 hours, however, as we mentioned earlier, the result of the TIMEDIFF function is a TIME value which ranges from -838:59:59 to 838:59:59. Let’s see what it is by using the SHOW WARNINGS statement. It returns the following result: + -+Īs you can see, we got one warning. mysql> SELECT TIMEDIFF(Ĭode language: SQL (Structured Query Language) ( sql ) MySQL TIMEDIFF function and truncated incorrect time valueĬonsider the following example. If you pass two arguments with different types, one is DATETIME and the other is TIME, the TIMEDIFF function also returns NULL. The TIMEDIFF function returns NULL if either argument is NULL. The following example calculates the difference between two DATETIME values: mysql> SELECT TIMEDIFF( ' 01:00:00', In this example, we calculated the difference between 12:00:00 and 10:00:00 that results in 02:00:00. ![]() Let’s take an example that calculates the difference between two time values. To compare a difference between two DATE or DATETIME values, you use the DATEDIFF function. It’s important to note that the TIMEDIFF function accepts values with TIME or DATETIME types. The TIMEDIFF function returns the result of dt1 - dt2 expressed as a time value.īecause the TIMEDIFF function returns a TIME value, its result is limited to the range allowed for TIME values which is from -838:59:59 to 838:59:59. The TIMEDIFF function accepts two arguments that must be the same type, either TIME or DATETIME. TIMEDIFF(dt1, dt2) Ĭode language: SQL (Structured Query Language) ( sql ) See the following syntax of TIMEDIFF function. The TIMEDIFF returns the difference between two TIME or DATETIME values. Summary: this tutorial shows you how to use the MySQL TIMEDIFF function and provides you with some important usage notes of the function.
0 Comments
Leave a Reply. |