A non-rigorous, non-scientific, totally off-the-cuff test of which function to pick when you need to group by year and month.<\/p>\n
I populated a table with 262K rows of random dates, and then ran <\/p>\n
with various functions, which should all result in the same grouping. I repeated each query five times and show the average time, using three different column types DATE, DATETIME, and TIMESTAMP.<\/p>\n And just in case you want to extract a full date (which really only makes sense for datetime and timestamp):<\/p>\n A non-rigorous, non-scientific, totally off-the-cuff test of which function to pick when you need to group by year and month. I populated a table with 262K rows of random dates, and then ran SELECT %s, COUNT(*) FROM table GROUP BY %s ORDER BY NULL with various functions, which should all result in the same grouping. […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_feature_clip_id":0,"_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,"font":"","enabled":false},"version":2},"jetpack_post_was_ever_published":false},"categories":[4],"tags":[],"class_list":["post-94","post","type-post","status-publish","format-standard","hentry","category-mysql"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/p2IBF1-1w","jetpack_sharing_enabled":true,"jetpack-related-posts":[],"_links":{"self":[{"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=\/wp\/v2\/posts\/94","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=94"}],"version-history":[{"count":1,"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=\/wp\/v2\/posts\/94\/revisions"}],"predecessor-version":[{"id":115,"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=\/wp\/v2\/posts\/94\/revisions\/115"}],"wp:attachment":[{"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=94"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=94"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=94"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}SELECT %s, COUNT(*) FROM table GROUP BY %s ORDER BY NULL<\/code><\/p>\n\n
\n expression<\/th>\n DATE<\/th>\n DATETIME<\/th>\n TIMESTAMP<\/th>\n<\/tr>\n \n EXTRACT(YEAR_MONTH FROM d)<\/td>\n 0.362<\/td>\n 0.369<\/td>\n 0.581<\/td>\n<\/tr>\n \n LAST_DAY(d)<\/td>\n 0.374<\/td>\n 0.389<\/td>\n 0.582<\/td>\n<\/tr>\n \n DATE_SUB(d, INTERVAL DAY(d) DAY)<\/td>\n 0.429<\/td>\n 0.882<\/td>\n 1.452<\/td>\n<\/tr>\n \n d – INTERVAL DAY(d) DAY<\/td>\n 0.429<\/td>\n 0.887<\/td>\n 1.535<\/td>\n<\/tr>\n \n SUBSTRING(d, 1, 7)<\/td>\n 0.454<\/td>\n 0.488<\/td>\n 0.719<\/td>\n<\/tr>\n \n YEAR(d), MONTH(d)<\/td>\n 1.046<\/td>\n 1.126<\/td>\n 2.045<\/td>\n<\/tr>\n \n MONTH(d), YEAR(d)<\/td>\n 1.116<\/td>\n 1.196<\/td>\n 2.112<\/td>\n<\/tr>\n \n LEFT(d, 7)<\/td>\n 1.307<\/td>\n 1.405<\/td>\n 2.123<\/td>\n<\/tr>\n \n DATE_FORMAT(d, ‘%Y%m’)<\/td>\n 1.480<\/td>\n 1.565<\/td>\n 2.112<\/td>\n<\/tr>\n \n DATE_FORMAT(d, ‘%Y-%m’)<\/td>\n 1.514<\/td>\n 1.615<\/td>\n 2.564<\/td>\n<\/tr>\n \n DATE_FORMAT(d, ‘%m%Y’)<\/td>\n 1.517<\/td>\n 1.604<\/td>\n 2.420<\/td>\n<\/tr>\n \n DATE_FORMAT(d, ‘%m-%Y’)<\/td>\n 1.562<\/td>\n 1.656<\/td>\n 2.465<\/td>\n<\/tr>\n \n MONTHNAME(d), YEAR(d)<\/td>\n 1.613<\/td>\n 1.713<\/td>\n 2.812<\/td>\n<\/tr>\n \n YEAR(d), MONTHNAME(d)<\/td>\n 1.663<\/td>\n 1.766<\/td>\n 2.873<\/td>\n<\/tr>\n<\/table>\n \n
\n expression<\/th>\n DATE<\/th>\n DATETIME<\/th>\n TIMESTAMP<\/th>\n<\/tr>\n \n DATE(d)<\/td>\n 0.357<\/td>\n 0.374<\/td>\n 0.591<\/td>\n<\/tr>\n \n EXTRACT(YEAR_MONTH FROM d), EXTRACT(DAY FROM d)<\/td>\n 0.377<\/td>\n 0.407<\/td>\n 0.730<\/td>\n<\/tr>\n \n EXTRACT(DAY FROM d), EXTRACT(YEAR_MONTH FROM d)<\/td>\n 0.395<\/td>\n 0.422<\/td>\n 0.751<\/td>\n<\/tr>\n \n MONTH(d), YEAR(d), DAY(d)<\/td>\n 0.395<\/td>\n 0.426<\/td>\n 0.870<\/td>\n<\/tr>\n \n YEAR(d), DAY(d), MONTH(d)<\/td>\n 0.398<\/td>\n 0.440<\/td>\n 0.862<\/td>\n<\/tr>\n \n YEAR(d), MONTH(d), DAY(d)<\/td>\n 0.406<\/td>\n 0.441<\/td>\n 0.867<\/td>\n<\/tr>\n \n DAY(d), YEAR(d), MONTH(d)<\/td>\n 0.409<\/td>\n 0.444<\/td>\n 0.859<\/td>\n<\/tr>\n \n LEFT(d, 10)<\/td>\n 0.437<\/td>\n 0.487<\/td>\n 0.728<\/td>\n<\/tr>\n \n SUBSTRING_INDEX(d, ‘ ‘, 1)<\/td>\n 0.439<\/td>\n 0.475<\/td>\n 0.743<\/td>\n<\/tr>\n \n DAY(d), MONTH(d), YEAR(d)<\/td>\n 0.441<\/td>\n 0.477<\/td>\n 0.901<\/td>\n<\/tr>\n \n MONTH(d), DAY(d), YEAR(d)<\/td>\n 0.442<\/td>\n 0.472<\/td>\n 0.914<\/td>\n<\/tr>\n \n SUBSTRING(d, 1, 10)<\/td>\n 0.460<\/td>\n 0.496<\/td>\n 0.729<\/td>\n<\/tr>\n \n DATE_FORMAT(d, ‘%d%Y%m’)<\/td>\n 0.539<\/td>\n 0.571<\/td>\n 0.852<\/td>\n<\/tr>\n \n DATE_FORMAT(d, ‘%m%Y%d’)<\/td>\n 0.542<\/td>\n 0.570<\/td>\n 0.841<\/td>\n<\/tr>\n \n DATE_FORMAT(d, ‘%m%d%Y’)<\/td>\n 0.543<\/td>\n 0.572<\/td>\n 0.846<\/td>\n<\/tr>\n \n DATE_FORMAT(d, ‘%Y%m%d’)<\/td>\n 0.544<\/td>\n 0.570<\/td>\n 0.842<\/td>\n<\/tr>\n \n DATE_FORMAT(d, ‘%Y%d%m’)<\/td>\n 0.544<\/td>\n 0.572<\/td>\n 0.843<\/td>\n<\/tr>\n \n DATE_FORMAT(d, ‘%d-%Y-%d’)<\/td>\n 0.547<\/td>\n 0.574<\/td>\n 0.848<\/td>\n<\/tr>\n \n DATE_FORMAT(d, ‘%d%m%Y’)<\/td>\n 0.549<\/td>\n 0.573<\/td>\n 0.842<\/td>\n<\/tr>\n \n DATE_FORMAT(d, ‘%m-%Y-%d’)<\/td>\n 0.552<\/td>\n 0.583<\/td>\n 0.869<\/td>\n<\/tr>\n \n DATE_FORMAT(d, ‘%Y-%m-%d’)<\/td>\n 0.558<\/td>\n 0.583<\/td>\n 0.854<\/td>\n<\/tr>\n \n DATE_FORMAT(d, ‘%Y-%d-%m’)<\/td>\n 0.569<\/td>\n 0.593<\/td>\n 0.867<\/td>\n<\/tr>\n \n d – INTERVAL HOUR(d) HOUR – INTERVAL MINUTE(d) MINUTE – INTERVAL SECOND(d) SECOND<\/td>\n 0.573<\/td>\n 0.653<\/td>\n 1.249<\/td>\n<\/tr>\n \n DATE_FORMAT(d, ‘%d-%m-%Y’)<\/td>\n 0.598<\/td>\n 0.637<\/td>\n 0.920<\/td>\n<\/tr>\n \n DATE_FORMAT(d, ‘%m-%d-%Y’)<\/td>\n 0.601<\/td>\n 0.633<\/td>\n 0.904<\/td>\n<\/tr>\n<\/table>\n","protected":false},"excerpt":{"rendered":"