Skip to content

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

MySQL Data Types July 27, 2011July 31, 2011

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

Of course, this works when timestamps are in a one second resolution. Which leads me to:

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

- Twitter
- LinkedIn
- WhatsApp
- Email
- Facebook
- Print

###
13
Leave a Reply

12 Comment threads
1 Thread replies
0 Followers
Most reacted comment
Hottest comment thread
10 Comment authors
Recent comment authors

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

Subscribe
Notify of
Guest
dror

Shouldn’t you use adddate() for the 2nd condition?

Guest
Baron Schwartz

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.

Guest
Keith Larson

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.

Guest
Roland Bouman

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.

Guest
Justin Rovang

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

😉

Author
shlomi

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

Author
shlomi

@Justin LOL!

Guest
Kedar

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) !! 😉

Guest
Scott David Daniels

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.

Author
shlomi

@Kedar,
Fixed, thanks!

Guest
Jack Y

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.

Author
shlomi

@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 »

Guest
cheolhwan.oh

DATEADD(ms,-2, DATEADD(d,DATEDIFF(d,-1,GETDATE()),0) )

wpDiscuz
Send to Email Address Your Name Your Email Address Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.