How should I filter dates in MySQL?

datetime, mysql, php

Solution

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.

Problem

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?

Original source