How would you check whether some TIMESTAMP column falls within a given day, say July 26th, 2011?
This is a question I tend to ask students, and usually I get the same range of answers. Some are wrong; some cannot utilize an index, some are correct, and some are temporarily correct. I wish to take a closer look at the last. Such an answer looks like this:
SELECT * FROM some_table WHERE timstamp_column BETWEEN '2011-07-26 00:00:00' AND '2011-07-26 23:59:59'
Yikes! I get my allergies when I see this one.
Technically this seems correct. And it seems to work so far for people. There are two things that disturb me:
- ’23:59:59′ refers to the beginning of the last second of the day. This means a full second is missing in concept. No one would write such a code on a C, Java or PHP application: that would be a newbie’s mistake. But people feel at ease doing it with SQL
- One second resolution is a temporary issue. Already MariaDB 5.3 presents with microsecond support for NOW() and TIMESTAMP, TIME and DATETIME columns. Sometime in the not-so-far future the standard MySQL distribution will have that, too.
Of course, this works when timestamps are in a one second resolution. Which leads me to:
Soon people will start losing data, reporting wrong numbers, perform incorrect aggregations. My advise:
grep -iR "23:59:59" /path/to/application/code/*
And get rid of it. A good alternative would be:
SELECT * FROM some_table WHERE timstamp_column >= DATE('2011-07-26') AND timestamp_column < (DATE('2011-07-26') + INTERVAL 1 DAY)
(Why use all this INTERVAL stuff? Think 28/29/30/31 days in month and get the answer)
And do not use LIKE ‘2011-07-26 %’. Read this if you’re not sure why.
@dror,
adddate() is fine. I tend to favor the use of INTERVAL, because it is such more intuitive for me.
@Baron
Yes, BETWEEN is inclusive, which is why it works correctly for now — for as long precision is 1 second.
@Roland,
Yes, leap seconds, daylight savings etc. are all so nicely managed by time/date math within MySQL, I usually drop all application based calculations and just ask the DB to do the math.
@Justin LOL!
Well Said!
+ date(timstamp_column)=’2011-07-26′ will work but this won’t catch the indexes.
BTW is it July 26th, 2011 or July 26th, 2001 (check ur query) !! 😉
I’d typically do:
SELECT … WHERE timestamp_column BETWEEN ‘2001-07-26’ AND ‘2001-07-07’ AND timestamp_column < '2001-07-27';
As some query optimizers are happier with the BETWEEN construct (treating it as a more restrictive query than a pair of comparisons). This is simply because I try for the most portable queries that are effective.
@Kedar,
Fixed, thanks!