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?

Quassnoi · Accepted Answer · 2009-06-16 17:17:45Z

7

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.

answered Jun 16, 2009 at 17:17

Quassnoi

427k94 gold badges628 silver badges623 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Powerlord Over a year ago

Wouldn't the BETWEEN operator be more appropriate here?

Powerlord Over a year ago

Hmm, I guess not, as you have to do CASTs in MySQL with BETWEEN on dates: dev.mysql.com/doc/refman/5.0/en/…

Quassnoi Over a year ago

@R.Bemrose: it would return the records that fall on '2009-07-01 00:00:00' exactly for both June and July queries. It can be a problem.

DisgruntledGoat Over a year ago

Thanks, this worked a treat. For some reason it didn't occur to me that I could use < > selectors...

dkretz · Accepted Answer · 2009-06-16 17:31:18Z

1

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".

edited Jun 16, 2009 at 17:31

answered Jun 16, 2009 at 17:17

dkretz

37.6k13 gold badges84 silver badges140 bronze badges

Collectives™ on Stack Overflow

How should I filter dates in MySQL?

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related