23:59:59 is not the end of the day. No, really!

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:

  1. ’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
  2. Of course, this works when timestamps are in a one second resolution. Which leads me to:

  3. 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.

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.

13 thoughts on “23:59:59 is not the end of the day. No, really!

  1. 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.

  2. 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’ ”

    😉

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.