如何在查询中的MYSQL日期中增加天数 [英] How can I add days to a date in MYSQL in a query

问题描述

我正在尝试在查询中的MYSQL日期中添加5天.这就是我所做的:

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.

推荐答案

您似乎想要的行end_date早于五天前.

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

最好的方法是

 WHERE end_date >= CURDATE() - INTERVAL 5 DAY

将整数添加到日期的业务在MySQL中不起作用(这是Oracle的事情).因此，您需要使用INTERVAL n unit语法.

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.

您会注意到，我上面的WHERE子句在功能上等同于

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.

1. 如果您在WHERE子句中提及end_date而不将其包装在计算中，则您的查询可以利用该列上的索引，并且可以更快地运行.
2. DATE(NOW())和CURDATE()均指今天的第一时刻(午夜).但是CURDATE()有点简单.

1. 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.
2. DATE(NOW()) and CURDATE() both refer to the first moment of today (midnight). But CURDATE() is a bit simpler.

这篇关于如何在查询中的MYSQL日期中增加天数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！