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.
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 queries for both (they look the same, don’t they?).
DATEADD(ms,-2, DATEADD(d,DATEDIFF(d,-1,GETDATE()),0) )