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.
Shouldn’t you use adddate() for the 2nd condition?
BETWEEN is inclusive on the upper end, so BETWEEN … AND … :59 will include every second of the day when there is only one-second precision. But you are right that it is better to do a strictly less-than the next day.
That was an often debated coding style at a previous company I worked for. I agree I like the use of intervals versus the 235959.
Perhaps far-fetched, and probably not applicable to the majority of the MySQL installations (see: http://dev.mysql.com/doc/refman/5.0/en/time-zone-leap-seconds.html) but in the rare case of a leap second, < date + INTERVAL 1 DAY) will always be correct, whereas = ..:59 may be off by one or two seconds.
Now your students will use “SELECT * FROM some_table WHERE timstamp_column BETWEEN ‘2001-07-26 00:00:00’ AND ‘2001-07-26 23:59:59.999999’ ”
😉