INTERVAL syntax question

[Antnee]

I have a simple query (I think). I want to allow a user to specify the interval in days, months and years. I want to put in a standard query and I've got this at the moment:

SELECT *
FROM table
WHERE date_sub(date_sub(date_sub(curdate(), INTERVAL 5 DAY), INTERVAL 3 MONTH), INTERVAL 0 YEAR) > date(updated)
ORDER by updated ASC

Is that the best way to do it? Or is there a better way? It certainly seems to work as it is but I imagine it could be faster

[r937]

Is that the best way to do it?

no

when you apply a function to a column, like DATE(updated), you do not get the benefit of indexed retrieval

better is to put the column on one side of the operator, and the calculations on the other

i suspect you can simply do

WHERE updated > ... some calculation

without worrying about the time component

as for the date calculations, be careful

if today is june 5th, then 5 days ago is may 31st, and 3 months before that is february the what?

[Antnee]

OK, I hear what you're saying. I was previously told to do it that way around, but I never understood why. Can you come up with a better way for me to do the calculation though? In all likelihood it will only ever use the month value but the option has to be there for further fine tuning. I'm using it to define an archive date. At the moment we just move jobs that are over 6 months in to an archive table as that data is rarely used any more.

I don't like your example about the dates though. What happens if I just use INTERVAL and we go back in to a date that doesn't exist? It works fine, MySQL is designed to handle it fine. When it's selecting rows that are dated prior to 'February 31st' then it will find everything up to the end of the month and then nothing else. That's what I expect it to do