select * from table where datetime in month (without breaking index)

Question

I have a bunch of timestamped rows (using the 'datetime' data type)

I want to select all the rows that have a timestamp that is within a particular month.

The column is indexed so I can't do MONTH(timestamp) = 3 because that'll make this index unuseable.

If I have year and month variables (in perl), is there a horrific bit of SQL I can use like:

timestamp BETWEEN DATE($year, $month, 0) AND DATE($year, $month, 31);

But nicer, and actually works?

Answer 1

I would actually go with the idea you proposed ; maybe with a small difference :

select *
from your_table 
where date_field >= '2010-01-01'
    and date_field < '2010-02-01'

(Of course, up to you the use $year and $month properly)

Note the < '2010-02-01' part : you might have to consider this, if you have dates that include the time.

For instance, if you have a line with a date like '2010-01-31 12:53:12', you probably want to have that line selected -- and, by default, '2010-01-31' means '2010-01-31 00:00:00'.

Maybe that doesn't look 'nice' to the eye ; but it'll work ; and use the index... It's the kind of solution I generaly use when I have that kind of problem.

Answer 2

If you need the same month of every year the index you have will not help you and no amount of SQL syntax trickery will help you

On the other hand if you need a month of a particular year then any query with date ranges should do it

Answer 3

Another alternative is to add an extra column to the table that stores the month, precomputed. That would just be a simple int column, and is trivial to index. Unless you're dealing with a kajillion rows, the extra space for an unsigned tiny int is neglible (one byte + db overhead per row).

It'd require a bit of extra work to keep synched with the timestamp column, but that's what triggers are for.

Answer 4

This is substantively Pascal MARTIN's answer, but avoids having to know explicitly what the next year/month is (so you don't have to increment year and wrap around the $month, when $month == 12):

my $sth = $mysql_dbh->prepare(<<__EOSQL);
SELECT ...
  FROM tbl
 WHERE ts >= ? AND ts < (? + INTERVAL 1 MONTH)
__EOSQL

my $yyyymm = $year . '-' . sprintf('%02d', $month);

$sth->execute($yyyymm, $yyyymm);

For bonus fugly points, you could also do this:

... WHERE ts BETWEEN ? AND (? + INTERVAL 1 MONTH - INTERVAL 1 SECOND)

That - INTERVAL 1 SECOND will coerce the upper boundary from a DATE into a DATETIME/TIMESTAMP type set to the last second of a day, which is, as Pascal indicated, what you want on the upper bound.

Answer 5

How about WHERE MONTH(`date`) = '$month' AND YEAR(`date`) = '$year'