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.