mysql 日期加天,如何在查询中的MYSQL日期中增加天数

I am trying to add 5 days to a date in MYSQL in a query. This is what I have done:

SELECT * FROM sales INNER JOIN partner on user_id = idpartner WHERE DATE((end_date) + 5) >= DATE(NOW()) ORDER BY end_date ASC LIMIT 0,50000

But this is not showing the list of sales which has ended. Can someone please tell me where I am making a mistake.

解决方案

It looks like you want rows where end_date is later than five days ago.

The best way to get that is with

WHERE end_date >= CURDATE() - INTERVAL 5 DAY

The business of adding integers to dates doesn't work in MySQL (it's an Oracle thing). So you need to use the INTERVAL n unit syntax.

You'll notice that my WHERE clause above is functionally equivalent to

WHERE DATE(end_date) + INTERVAL 5 DAY >= DATE(NOW())

But, the first formulation is superior to the second for two reasons.

if you mention end_date in a WHERE clause without wrapping it in computations, your query can exploit an index on that column and can run faster.

DATE(NOW()) and CURDATE() both refer to the first moment of today (midnight). But CURDATE() is a bit simpler.