{"id":3839,"date":"2011-07-27T20:16:00","date_gmt":"2011-07-27T18:16:00","guid":{"rendered":"http:\/\/code.openark.org\/blog\/?p=3839"},"modified":"2011-07-31T07:42:49","modified_gmt":"2011-07-31T05:42:49","slug":"235959-is-not-the-end-of-the-day-no-really","status":"publish","type":"post","link":"https:\/\/code.openark.org\/blog\/mysql\/235959-is-not-the-end-of-the-day-no-really","title":{"rendered":"23:59:59 is not the end of the day. No, really!"},"content":{"rendered":"

How would you check whether some TIMESTAMP<\/strong> column falls within a given day, say July 26th, 2011<\/strong>?<\/p>\n

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<\/em>. I wish to take a closer look at the last. Such an answer looks like this:<\/p>\n

\n
SELECT * FROM some_table WHERE timstamp_column BETWEEN '2011-07-26 00:00:00' AND '2011-07-26 23:59:59'<\/pre>\n<\/blockquote>\n
Yikes! I get my allergies when I see this one.<\/p>\n
Technically this seems correct. And it seems to work so far for people. There are two things that disturb me:<\/p>\n
    \n
  1. ’23:59:59′<\/strong> 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<\/li>\n
    Of course, this works when timestamps are in a one second resolution. Which leads me to:<\/p>\n
  2. One second resolution is a temporary issue. Already MariaDB 5.3<\/a> 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.<\/li>\n<\/ol>\n
    Soon people will start losing data, reporting wrong numbers, perform incorrect aggregations. My advise:<\/p>\n
    \n
    grep -iR \"23:59:59\" \/path\/to\/application\/code\/*<\/pre>\n<\/blockquote>\n
    And get rid of it. A good alternative would be:<\/p>\n
    \n
    SELECT * FROM some_table WHERE timstamp_column >= DATE('2011-07-26') AND timestamp_column < (DATE('2011-07-26') + INTERVAL 1 DAY)<\/pre>\n<\/blockquote>\n
    (Why use all this INTERVAL stuff? Think 28\/29\/30\/31<\/strong> days in month and get the answer)<\/p>\n
    And do not use LIKE ‘2011-07-26 %’<\/strong>. Read this<\/a> if you’re not sure why.<\/p>\n","protected":false},"excerpt":{"rendered":"
    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 […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"enabled":false},"version":2}},"categories":[5],"tags":[18],"class_list":["post-3839","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-data-types"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2bZZp-ZV","_links":{"self":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/3839","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/comments?post=3839"}],"version-history":[{"count":12,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/3839\/revisions"}],"predecessor-version":[{"id":3859,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/3839\/revisions\/3859"}],"wp:attachment":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/media?parent=3839"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/categories?post=3839"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/tags?post=3839"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}