All the

Databases
1

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.

2

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 :slight_smile:

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

3

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

4

Your answer is very welcome and appreciated. Ty very much.

I own you a beer or a tea :slight_smile: