I have a table like this:
|id | task | created | expire |
CREATE TABLE schedule ( id INT( 11 ) NULL AUTO_INCREMENT PRIMARY KEY , task VARCHAR( 255 ) NOT NULL , created DATE NOT NULL , expire DATE NOT NULL
) ENGINE = MYISAM ;
I’ll like to get all the jobs which expire this month and were created a day before expiration time. And I’m stuck.
it is far more important to get the right answer, even if slowly, than to get the wrong answer blindingly fast
so your query gets the right answer, which is good
that said, your query can be improved for performance
whenever you apply a function to a column, like MONTH(expire) and YEAR(expire), mysql cannot utilize an index on that column (if one exists)
so you should definitely have an index on the expire column, and then allow it to be used to optimize the query as follows –
WHERE expire >= CURRENT_DATE - INTERVAL DAYOFMONTH(CURRENT_DATE)-1 DAY
DAYOFMONTH(CURRENT_DATE) would return 10 for today, the 10th of september, and you take one less than that, 9, and subtract that number of days from the current date, and the result will always be the first of the month
so this code says “where expire is greater or equal to the first day of the current month”
then you add
AND expire < CURRENT_DATE - INTERVAL DAYOFMONTH(CURRENT_DATE)-1 DAY
+ INTERVAL 1 MONTH
and this says “where expire is less than the first day of the following month”
this simply and elegantly sidesteps all that nasty february 29 nonsense which i’ve seen people try to code for when they have a date range equal to an entire month – just make it less than the following month
got it! sometimes helps only to write it down. ty sitepoint.
SELECT task FROM schedule
WHERE (
MONTH(expire) = MONTH(curdate())
AND
YEAR(expire) = YEAR(curdate())
)
AND created = DATE_SUB(expire, INTERVAL 1 DAY);
anyway, if you have a better approach, or a comment, please replay to this post. I’m on the learning stage of mysql