How do you write CURDATE plus 1 month?

PHP
1

Hi,
i’m trying to show results where the date of a job matches the current month + 1 month. E.g. Current month is March, i want to show April jobs…

How do i change the statement below to get that result?

$query_jobs = “SELECT * FROM jobs WHERE EXTRACT(MONTH FROM predict_date) = EXTRACT(MONTH FROM CURDATE()) ORDER BY predict_date”;

Any help would be great.
Thanks.

2

EXTRACT(MONTH FROM CURDATE() + INTERVAL ‘1’ MONTH) should do the trick :slight_smile:

PS. Don’t you need the year in your query as well?
When I’m looking in december, I wouldn’t want to see results for january of the current year, which is what you’ll get if you don’t filter on year and those results are still in the database.

Another way to do it would be

WHERE predict_date BETWEEN CURDATE() AND CURDATE() + INTERVAL ‘1’ MONTH

This does take years into account

3

many thanks… that did it :slight_smile:

4

actually, no

that would give you the results for March 6th through April 6th, it will ~not~ give you all the results for April

here’s how to do it –

WHERE predict_date >= CURRENT_DATE 
                        - INTERVAL DAYOFMONTH(CURRENT_DATE) - 1 DAY
                        + INTERVAL 1 MONTH
  AND predict_date  < CURRENT_DATE 
                        - INTERVAL DAYOFMONTH(CURRENT_DATE) - 1 DAY
                        + INTERVAL 2 MONTH

remember in this type of condition, if you want the query to be optimized and use the index on the column, put the column on the left of the comparison operator, and do all the calculations on the right

as soo as you do FUNCTION(predict_date) > … you get a table scan