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?
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.
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.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With