Efficient SELECT query to find records within a month

Question

I have a MySQL DB table with multiple date type fields. I need to do different SELECT queries on this table but I am not sure which way is the best to find records from the same month.

I know I can do the following:

SELECT *
  FROM table
 WHERE MONTH(somedate) = 5
   AND YEAR(somedate) = 2015

But I keep reading that isn't efficient and that I should go with using actual dates, i.e.

SELECT *
  FROM table
 WHERE somedate BETWEEN '2015-05-01' AND '2015-05-31'

However, all I would have is the month and the year as variables coming in from PHP. How do I easily and quickly calculate the last day of the month if I go with second option?

Answer 1

Don't calculate the last day of the month. Calculate the first day of the next month instead.

Your query can be like this

WHERE t.mydatetimecol >= '2015-05-01'
  AND t.mydatetimecol  < '2015-05-01' + INTERVAL 1 MONTH

Note that we're doing a less than comparison, not a "less than or equal to"... this is very convenient for comparing TIMESTAMP and DATETIME columns, which can include a time portion.

Note that a BETWEEN comparison is a "less than or equal to". To get a comparison equivalent to the query above, we'd need to do

WHERE t.mydatetimecol
      BETWEEN '2015-05-01' AND '2015-05-01' + INTERVAL 1 MONTH + INTERVAL -1 SECOND 

(This assumes that the resolution of DATETIME and TIMESTAMP is down to a second. In other databases, such as SQL Server, the resolution is finer than a second, so there we'd have the potential of missing a row with value of '2015-05-31 23:59:59.997'. We don't have a problem like that with the less than the first day of the next month comparison... < '2015-06-01'

No need to do the month or date math yourself, let MySQL do it for you. If you muck with adding 1 to the month, you have to handle the rollover from December to January, and increment the year. MySQL has all that already builtin.

Answer 2

date('t', strtotime("$year-$month-01")) will give days in the month