MySQL get/find/calculate the difference between two dates; In this tutorial, we would love to share with you how to use the MySQL DATEDIFF function to find or get difference between two dates in days.
MySQL DATEDIFF function; This DATEDIFF() function is used to get the difference between the two specified date values and return days.
MySQL DATEDIFF: Find/Calculate Days Between Two Dates
Basically the MySQL DATEDIFF function gives the difference between days between two date values.
Syntax of MySQL DATEDIFF Function
The MySQL DATEDIFF syntax is:
DATEDIFF( date1, date2 )
Parameters of MySQL DATEDIFF function
- date1 A date/datetime value
- date2 A date/datetime value
Examples of MySQL DATEDIFF to Find/Calculate Days Between Two Dates in Days
The MySQL DATEDIFF is useful in many cases. For example, you can calculate an interval in those days that customers need to ship the products.
When used with date values, only the date part is used to compare the date.
SELECT DATEDIFF('2019-10-10', '2019-02-15'); Output: 237
When used with datatime values, only the date part is used to compare the date.
SELECT DATEDIFF('2019-5-10 11:41:14', '2019-02-15 12:10:08'); Output: 84
Compare datetime and date values.
SELECT DATEDIFF('2019-4-10 11:41:14', '2019-02-15'); Output: 54
Must Know Date/Time FunctionsList Of MySQL Date/Time Functions
Two more exmaple with dates mysql> SELECT DATEDIFF('2019-10-25', '2019-10-10'); Output: 15 mysql> SELECT DATEDIFF('2019-12-10', '2019-10-31'); Output: 40
The date should not be later than the second date on the first date. You can use the earlier date for the first argument and it will return a negative value.
SELECT DATEDIFF('2018-12-31', '2019-10-10'); Output: -283
Here’s an example of using DATEDIFF() in a database query.
To calculate the number of days between created_at and updated_at of the users table, you use the DATEDIFF function as follows:
SELECT user_id, DATEDIFF(created_at, updated_at) daysDiff FROM users ORDER BY daysDiff DESC;
In this tutorial, you have learned how to use MySQL DATEDIFF function to calculate the number of days between two date values.