Redmond Militante wrote:
hi
i need advice on a query i'm trying to do. i'm trying to find entries with that are about to expire. entries expire if their date of submission is older than 60 days. i want to find all entries with a date of submission greater than 30 days, or those that are going to expire within a month
i'm using this as part of my query to find entries that are about to
expire: ' ... DATE_ADD($dbtable3.savedemployers2, INTERVAL 30 DAY)
<=NOW()'
am i doing this the right way?
thanks redmond
Almost. First, you say that your column is a DATE, but you are comparing to NOW(), which returns a DATETIME. That's OK (mysql will convert), but it would be slightly better to compare to CURDATE(), as it returns a DATE.
More importantly, you do not want to compare a function of a column to a constant, because then an index on the column is of no use, forcing a full table scan. If at all possible, move the function to the constant side of the comparison, as a function of a constant is a constant.
In other words,
datecol + 30 days <= today
is equivalent to
datecol <= today - 30 days
but the former forces a table scan while the latter could use an index on datecol.
So, in your case, you should use
WHERE ... $dbtable3.savedemployers2 <= CURDATE() - INTERVAL 30 DAY
Finally, note that this would also return entries which have already expired, if they are still there. To leave those out, change the condition to
$dbtable3.savedemployers2 BETWEEN CURDATE() - INTERVAL 60 DAY
AND CURDATE() - INTERVAL 30 DAYMichael
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
