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’ ”
😉
@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!
MySQL timestamp is 4 bytes so I can’t see why BETWEEN ‘2011-07-26 00:00:00’ AND ‘2011-07-26 23:59:59’ is wrong.
@Jack, BETWEEN ’2011-07-26 00:00:00′ AND ’2011-07-26 23:59:59′ is correct as long as timestamps are in 1 second resolution. Again: 23:59:59 is the *beginning* of the last second. When you have better resolution than 1 second (as already exists with MariaDB), this makes the query wrong, since it will *not* catch 23:59:59.01, for example. Perhaps I should have been more general and talk about “all the different kinds of temporal values, now and in the future”. Do also note than not many programmers are actually aware of the differences between TIMESTAMP and DATETIME. They choose either; they work out the same… Read more »
DATEADD(ms,-2, DATEADD(d,DATEDIFF(d,-1,GETDATE()),0) )