Most Efficient Way of Selecting Row by Month/Year?

I have a page where I only select the rows from my database that are in a particular month/year. The variable sent to each page is in YYYY-MM format.

To do my select query, I just did:

DATE_FORMAT(video.date,‘%Y-%m’) = ‘2009-12’

It works fine, I’m just making sure that this isn’t too resource intensive. Is there a better way that uses less resources in the query?

THx
Ryan

yes, there’s a better way

  1. make sure that video.date is indexed

  2. use a range test –

WHERE video_date >= '2009-12-01'
  AND video.date  < '2010-01-01'

Ok,

That’s what I was afraid of. So I’d have to take the YYYY-MM variable, add an “01” as the day and run it through some PHP coding to figure out the plus one on the month.

If you know I quick solution for adding a month I’m all ears.

Cheers
Ryan

adding a month in mysql or in php?

I ended up going with PHP.


$dateday = $date .'-01';
$datefuture = date("Y-m-d", strtotime(date("Y-m-d", strtotime($dateday)) . " +1 month"));

That gives me my one-month span for the query.

Cheers
Ryan

Just to clarify:

WHERE video_date >= ‘2009-12-01’
AND video.date < ‘2010-01-01’

That date format works properly with datetime columns, correct? It’s working, just want to make sure it isn’t missing anything from the first day due to time.

Cheers
Ryan