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. I repeated each query five times and show the average time, using three different column types DATE, DATETIME, and TIMESTAMP.
expression | DATE | DATETIME | TIMESTAMP |
---|---|---|---|
EXTRACT(YEAR_MONTH FROM d) | 0.362 | 0.369 | 0.581 |
LAST_DAY(d) | 0.374 | 0.389 | 0.582 |
DATE_SUB(d, INTERVAL DAY(d) DAY) | 0.429 | 0.882 | 1.452 |
d – INTERVAL DAY(d) DAY | 0.429 | 0.887 | 1.535 |
SUBSTRING(d, 1, 7) | 0.454 | 0.488 | 0.719 |
YEAR(d), MONTH(d) | 1.046 | 1.126 | 2.045 |
MONTH(d), YEAR(d) | 1.116 | 1.196 | 2.112 |
LEFT(d, 7) | 1.307 | 1.405 | 2.123 |
DATE_FORMAT(d, ‘%Y%m’) | 1.480 | 1.565 | 2.112 |
DATE_FORMAT(d, ‘%Y-%m’) | 1.514 | 1.615 | 2.564 |
DATE_FORMAT(d, ‘%m%Y’) | 1.517 | 1.604 | 2.420 |
DATE_FORMAT(d, ‘%m-%Y’) | 1.562 | 1.656 | 2.465 |
MONTHNAME(d), YEAR(d) | 1.613 | 1.713 | 2.812 |
YEAR(d), MONTHNAME(d) | 1.663 | 1.766 | 2.873 |
And just in case you want to extract a full date (which really only makes sense for datetime and timestamp):
expression | DATE | DATETIME | TIMESTAMP |
---|---|---|---|
DATE(d) | 0.357 | 0.374 | 0.591 |
EXTRACT(YEAR_MONTH FROM d), EXTRACT(DAY FROM d) | 0.377 | 0.407 | 0.730 |
EXTRACT(DAY FROM d), EXTRACT(YEAR_MONTH FROM d) | 0.395 | 0.422 | 0.751 |
MONTH(d), YEAR(d), DAY(d) | 0.395 | 0.426 | 0.870 |
YEAR(d), DAY(d), MONTH(d) | 0.398 | 0.440 | 0.862 |
YEAR(d), MONTH(d), DAY(d) | 0.406 | 0.441 | 0.867 |
DAY(d), YEAR(d), MONTH(d) | 0.409 | 0.444 | 0.859 |
LEFT(d, 10) | 0.437 | 0.487 | 0.728 |
SUBSTRING_INDEX(d, ‘ ‘, 1) | 0.439 | 0.475 | 0.743 |
DAY(d), MONTH(d), YEAR(d) | 0.441 | 0.477 | 0.901 |
MONTH(d), DAY(d), YEAR(d) | 0.442 | 0.472 | 0.914 |
SUBSTRING(d, 1, 10) | 0.460 | 0.496 | 0.729 |
DATE_FORMAT(d, ‘%d%Y%m’) | 0.539 | 0.571 | 0.852 |
DATE_FORMAT(d, ‘%m%Y%d’) | 0.542 | 0.570 | 0.841 |
DATE_FORMAT(d, ‘%m%d%Y’) | 0.543 | 0.572 | 0.846 |
DATE_FORMAT(d, ‘%Y%m%d’) | 0.544 | 0.570 | 0.842 |
DATE_FORMAT(d, ‘%Y%d%m’) | 0.544 | 0.572 | 0.843 |
DATE_FORMAT(d, ‘%d-%Y-%d’) | 0.547 | 0.574 | 0.848 |
DATE_FORMAT(d, ‘%d%m%Y’) | 0.549 | 0.573 | 0.842 |
DATE_FORMAT(d, ‘%m-%Y-%d’) | 0.552 | 0.583 | 0.869 |
DATE_FORMAT(d, ‘%Y-%m-%d’) | 0.558 | 0.583 | 0.854 |
DATE_FORMAT(d, ‘%Y-%d-%m’) | 0.569 | 0.593 | 0.867 |
d – INTERVAL HOUR(d) HOUR – INTERVAL MINUTE(d) MINUTE – INTERVAL SECOND(d) SECOND | 0.573 | 0.653 | 1.249 |
DATE_FORMAT(d, ‘%d-%m-%Y’) | 0.598 | 0.637 | 0.920 |
DATE_FORMAT(d, ‘%m-%d-%Y’) | 0.601 | 0.633 | 0.904 |