{"id":1666,"date":"2009-12-08T11:46:24","date_gmt":"2009-12-08T09:46:24","guid":{"rendered":"http:\/\/code.openark.org\/blog\/?p=1666"},"modified":"2009-12-08T11:46:24","modified_gmt":"2009-12-08T09:46:24","slug":"useful-temporal-functions-queries","status":"publish","type":"post","link":"https:\/\/code.openark.org\/blog\/mysql\/useful-temporal-functions-queries","title":{"rendered":"Useful temporal functions & queries"},"content":{"rendered":"

Here’s a complication of some common and useful time & date calculations and equations. Some, though very simple, are often misunderstood, leading to inefficient or incorrect implementations.<\/p>\n

There are many ways to solve such problems. I’ll present my favorites.<\/p>\n

Querying for time difference<\/h4>\n

Given two timestamps: ts1<\/em> (older) and ts2<\/em> (newer), how much time has passed between them?<\/p>\n

One can use TIMEDIFF()<\/strong> & DATEDIFF()<\/strong>, or compare two UNIX_TIMESTAMP()<\/strong> values. My personal favorite is to use TIMESTAMPDIFF()<\/strong>. Reason being that I’m usually interested in a specific metric, like the number of hours which have passed, or the number of days, disregarding the smaller minute\/second resolution. Which allows one to:<\/p>\n

\n
SELECT TIMESTAMPDIFF(HOUR, ts1, ts2)<\/pre>\n<\/blockquote>\n
Take, for example:<\/p>\n
\n
SELECT TIMESTAMPDIFF(MONTH, '2008-10-07 00:00:00', '2009-12-06 00:00:00')<\/pre>\n<\/blockquote>\n
The function correctly identifies the number of days per month, and provides with 13<\/strong>, being the truncated number of full months.<\/p>\n
Doing arithmetics<\/h4>\n
One can use TIMESTAMPADD()<\/strong>, or DATE_SUB()<\/strong>, but, again, when dealing with specific resolutions, I find “+ INTERVAL<\/strong>” to be the most convenient:<\/p>\n
\n
SELECT ts1 + INTERVAL 10 HOUR<\/pre>\n<\/blockquote>\n
This allows me to only add by a specific unit: SECOND<\/strong>, MINUTE<\/strong>, HOUR<\/strong>, DAY<\/strong>, WEEK<\/strong>, etc. Many times I find this is exactly what I want.<\/p>\n
\n
SELECT TIMESTAMP('2009-12-06 20:14:52') + INTERVAL 4 WEEK AS ts2;\r\n+---------------------+\r\n| ts2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n+---------------------+\r\n| 2010-01-03 20:14:52 |\r\n+---------------------+<\/pre>\n<\/blockquote>\n
Checking if a timestamp is in a given date<\/h4>\n
This one is very popular, and most poorly treated.<\/p>\n
Say we have a sales<\/strong> table, with some ts<\/strong> column. We want to SELECT all sales on Dec 25th, 2008. I’ve seen so many solutions, many in writing. Let’s look at them:<\/p>\n
Wrong:<\/em><\/p>\n
\n
SELECT * FROM sales WHERE ts BETWEEN '2008-12-25' AND '2008-12-26'<\/pre>\n<\/blockquote>\n
Why is this wrong? Because BETWEEN<\/strong> is inclusive. A sale taking place on ‘2008-12-26 00:00:00<\/strong>‘ will match our condition.<\/p>\n
Correct but inefficient:<\/em><\/p>\n
\n
SELECT * FROM sales WHERE DATE(ts) = DATE('2008-12-25')<\/pre>\n<\/blockquote>\n
Why is this inefficient? Because a function is used over the ts<\/strong> column. This disables use of any index we might have on ts<\/strong>, leading to full table scan.<\/p>\n
Correct but inefficient:<\/em><\/p>\n
\n
SELECT * FROM sales WHERE ts LIKE '2008-12-25 %'<\/pre>\n<\/blockquote>\n
Why is this inefficient? Because a function is used over the ts<\/strong> column. Can you see it? It’s an implicit CAST function, which casts the TIMESTAMP value to a character value, so as to perform a string comparison.<\/p>\n
Correct but ugh:<\/em><\/p>\n
\n
SELECT * FROM sales WHERE ts BETWEEN '2008-12-25 00:00:00' AND '2008-12-25 23:59:59'<\/pre>\n<\/blockquote>\n
Why is it ugh? Because, well, …Ugh!<\/p>\n
Correct:<\/em><\/p>\n
\n
SELECT * FROM sales WHERE ts >= DATE('2008-12-25') AND ts < DATE('2008-12-26')<\/pre>\n<\/blockquote>\n
This allows for indexing to be used properly. The DATE()<\/strong> casting is not strictly required here, but is generally safer.<\/p>\n
Truncating to last midnight<\/h4>\n
Surprisingly, this simple question sees a lot of incorrect solution attempts. The quickest, safest way to get “last midnight” is:<\/p>\n
\n
SELECT DATE(ts)<\/pre>\n<\/blockquote>\n
or, if you like to be stricter:<\/p>\n
\n
SELECT TIMESTAMP(DATE(ts))<\/pre>\n<\/blockquote>\n
For example:<\/p>\n
\n
SELECT TIMESTAMP(DATE('2009-12-06 20:14:52')) AS midnight;\r\n+---------------------+\r\n| midnight\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n+---------------------+\r\n| 2009-12-06 00:00:00 |\r\n+---------------------+<\/pre>\n<\/blockquote>\n
Truncating to last round hour<\/h4>\n
Similar to the above, but utilizes arithmetic:<\/p>\n
\n
SELECT DATE(ts) + INTERVAL HOUR(ts) HOUR<\/pre>\n<\/blockquote>\n
For example:<\/p>\n
\n
SELECT ts, DATE(ts) + INTERVAL HOUR(ts) HOUR FROM sales LIMIT 5;\r\n+---------------------+-----------------------------------+\r\n| ts\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | DATE(ts) + INTERVAL HOUR(ts) HOUR |\r\n+---------------------+-----------------------------------+\r\n| 2009-01-05 05:17:00 | 2009-01-05 05:00:00\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n| 2009-03-09 00:49:00 | 2009-03-09 00:00:00\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n| 2009-02-20 00:14:00 | 2009-02-20 00:00:00\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n| 2009-02-14 22:42:00 | 2009-02-14 22:00:00\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n| 2009-03-14 04:50:00 | 2009-03-14 04:00:00\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n+---------------------+-----------------------------------+<\/pre>\n<\/blockquote>\n
Round to closest round hour<\/h4>\n
Taking the classic round() implementation, which states:<\/p>\n
\n
round(x) := int(x + 0.5)<\/pre>\n<\/blockquote>\n
We write:<\/p>\n
\n
SELECT DATE(ts + INTERVAL 30 MINUTE) + INTERVAL HOUR(ts + INTERVAL 30 MINUTE) HOUR<\/pre>\n<\/blockquote>\n
Example:<\/p>\n
\n
SELECT ts, DATE(ts + INTERVAL 30 MINUTE) + INTERVAL HOUR(ts + INTERVAL 30 MINUTE) HOUR AS rounded FROM sales ORDER BY HOUR(ts) DESC LIMIT 5;\r\n+---------------------+---------------------+\r\n| ts\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | rounded\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n+---------------------+---------------------+\r\n| 2009-03-25 23:54:00 | 2009-03-26 00:00:00 |\r\n| 2009-03-13 23:45:00 | 2009-03-14 00:00:00 |\r\n| 2009-01-29 22:53:00 | 2009-01-29 23:00:00 |\r\n| 2009-01-18 22:22:00 | 2009-01-18 22:00:00 |\r\n| 2009-01-14 22:16:00 | 2009-01-14 22:00:00 |\r\n+---------------------+---------------------+<\/pre>\n<\/blockquote>\n
Count number of midnights between two timestamps, inclusive<\/h4>\n
Given two timestamps, ts1<\/em> and ts2<\/em>, what is the number of midnights between them?<\/p>\n
\n
SELECT TIMESTAMPDIFF(DAY, DATE(ts1), ts2) + IF(DATE(ts1) = ts1, 1, 0);<\/pre>\n<\/blockquote>\n
Example:<\/p>\n
\n
SELECT ts, ts2, TIMESTAMPDIFF(DAY, DATE(ts), ts2) + IF(DATE(ts) = ts, 1, 0) AS number_of_midnights FROM sales LIMIT 10;\r\n+---------------------+---------------------+---------------------+\r\n| ts\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | ts2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | number_of_midnights |\r\n+---------------------+---------------------+---------------------+\r\n| 2009-01-05 05:17:00 | 2009-01-05 19:17:00 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0 |\r\n| 2009-03-09 00:49:00 | 2009-03-11 15:49:00 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2 |\r\n| 2009-02-20 00:14:00 | 2009-02-23 02:14:00 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 3 |\r\n| 2009-02-14 22:42:00 | 2009-02-18 07:42:00 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 4 |\r\n| 2009-03-14 04:50:00 | 2009-03-17 16:50:00 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 3 |\r\n| 2009-02-16 04:01:00 | 2009-02-19 08:01:00 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 3 |\r\n| 2009-01-20 05:36:00 | 2009-01-21 08:36:00 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\r\n| 2009-02-07 15:57:00 | 2009-02-07 22:57:00 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0 |\r\n| 2009-02-13 14:59:00 | 2009-02-15 22:59:00 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2 |\r\n| 2009-01-11 03:02:00 | 2009-01-13 11:02:00 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2 |\r\n+---------------------+---------------------+---------------------+<\/pre>\n<\/blockquote>\n
Further notes<\/h4>\n
A full listing of temporal functions can be found on the MySQL documentation<\/a>. There’s almost always more than one way to solve a problem. I’ve seen (and done, in the past) many calculations done on the application side due to lack of familiarity with the available functions.<\/p>\n
Please share your own common solutions below!<\/p>\n","protected":false},"excerpt":{"rendered":"
Here’s a complication of some common and useful time & date calculations and equations. Some, though very simple, are often misunderstood, leading to inefficient or incorrect implementations. There are many ways to solve such problems. I’ll present my favorites. Querying for time difference Given two timestamps: ts1 (older) and ts2 (newer), how much time has […]<\/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,26,21],"class_list":["post-1666","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-data-types","tag-indexing","tag-sql"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2bZZp-qS","_links":{"self":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/1666","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=1666"}],"version-history":[{"count":28,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/1666\/revisions"}],"predecessor-version":[{"id":1694,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/1666\/revisions\/1694"}],"wp:attachment":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/media?parent=1666"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/categories?post=1666"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/tags?post=1666"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}