返回顶部

OutOfMemory.CN技术专栏-> MySQL-> mysql函数-> MySQL DATEDIFF 函数

MySQL DATEDIFF 函数

更多

Summary: in this tutorial, you will learn how to use the MySQL DATEDIFF function to calculate the number of days between two date values.

MySQL DATEDIFF function syntax

MySQL DATEDIFF function calculate the number of days between two DATE, DATETIME, or TIMESTAMP values. The DATEDIFF function is useful in many cases e.g., you can calculate an interval in days that the products need to ship to a customer.

The syntax of the MySQL DATEDIFF function is as follows:

DATEDIFF(date_expression_1,date_expression_2)

The DATEDIFF function accepts two arguments which are two valid DATE, DATETIME or TIMESTAMP values. You can pass expressions that evaluate to DATE,DATETIME or TIMSTAMP values to the DATEDIFF function.

The DATEDIFF function returns the number of days between two DATE values i.e., date_expression_1 - date_expression_2.

Notice that the DATEDIFF function uses only date parts of the expressions for calculation. It ignores the time part if you pass the DATETIME or TIMESTAMP values.

MySQL DATEDIFF function examples

Let’s take a look at some  examples of using the   DATEDIFF function.

SELECT DATEDIFF('2011-08-17','2011-08-17'); --  0 day
SELECT DATEDIFF('2011-08-17','2011-08-08'); --  9 days
SELECT DATEDIFF('2011-08-08','2011-08-17'); -- -9 days

To calculate the number of days between required date and shipped date for orders in the orders table, you use the DATEDIFF function as follows:

SELECT orderNumber,
       DATEDIFF(requiredDate,shippedDate) daysLeft
FROM orders
ORDER BY daysLeft DESC;

MySQL DATEDIFF function query output

The following statement gets all orders whose statuses are in process and calculates the number of days between the ordered date and required date:

SELECT orderNumber, 
       DATEDIFF(requiredDate,orderDate) remaining_days
FROM orders
WHERE status = 'In Process'
ORDER BY remaining_days;

MySQL DATEDIFF function example

For calculating an interval in week or month, you can divide the returned value of the DATEDIFF function by 7 or 30 as the following query:

SELECT orderNumber, 
       DATEDIFF(requiredDate,orderDate) /7, 
       DATEDIFF(requiredDate,orderDate) /30 
FROM orders
WHERE status = 'In Process'

MySQL DATEDIFF week and month intervals

In this tutorial, you have learned how to use MySQL DATEDIFF function to calculate the number of days between two DATE or DATETIME values.

推荐阅读:
支持

0

反对

0

发表评论