How should I filter dates in MySQL?

Question

I'm creating a set of "archive" pages that are specified by year and month. In my table I have a datetime field called posted. I want to select all rows that are in a particular month.

I've thought of two solutions:

(1) Use string matching:

SELECT ... WHERE posted LIKE '2009-06%'

(2) Use some MySQL extraction functions:

SELECT ... WHERE YEAR(posted)=2009 AND MONTH(posted)=6

Which of these will be quicker, and are there any better solutions?

Solution 1

SELECT   *
WHERE    posted >= '2009-06-01'
         AND posted < '2009-07-01'

This one will efficiently use an index on posted, unlike both your queries.

Note that if your were keeping posted as a VARCHAR, then the following query:

SELECT ... WHERE posted LIKE '2009-06%'

would use the index too.

Solution 2

Disregarding reservations about this design, the standard syntax would be "BETWEEN first-date AND last-date", or (if you're using less than date granularity), "posted >= first-date AND posted < last-date + 1".