MENU
Programming / Cloud / Frameworks / Libraries / Design
  1. Home
  2. MySQL
  3. Aggregation by date and time (by day, by month, by day of the week)

Aggregation by date and time (by day, by month, by day of the week)

MySQL

This section introduces how to aggregate data by time axis, such as by day, day of the week, and month.

TOC

Test environment

Table Creation

CREATE TABLE `tests` (
  `column` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Insert 10,000 records for testing

First, execute the following query to store 10 records.

INSERT INTO `tests`
(`column`)
VALUES
('2000-01-01'),
('2000-01-01'),
('2000-01-01'),
('2000-01-01'),
('2000-01-01'),
('2000-01-01'),
('2000-01-01'),
('2000-01-01'),
('2000-01-01'),
('2000-01-01');

Six simple joins will result in 10,000 records.
(10 × 10 × 10 × 10 = 10,000)

mysql> SELECT COUNT(*) FROM `tests` t1, `tests` t2, `tests` t3, `tests` t4;
+----------+
| COUNT(*) |
+----------+
|    10000 |
+----------+
1 row in set (0.01 sec)

Add 10,000 records using simple joins.

mysql> INSERT INTO `tests`
    -> (`column`)
    -> SELECT `t1`.`column` FROM `tests` t1, `tests` t2, `tests` t3, `tests` t4;
Query OK, 10000 rows affected (0.19 sec)
Records: 10000  Duplicates: 0  Warnings: 0

Update to random date

Run the following query to update to a random date between 2015-01-01 and 730 days.

UPDATE `tests`
SET `column` = ADDTIME(CONCAT_WS(' ','2015-01-01' + INTERVAL RAND() * 730 DAY, '00:00:00'),
                       SEC_TO_TIME(FLOOR(0 + (RAND() * 86401))));

Aggregation

Aggregated by year

SELECT DATE_FORMAT(`column`, '%Y') as `grouping_column`,
       COUNT(`column`) as count
FROM `tests`
GROUP BY `grouping_column`;
mysql> SELECT DATE_FORMAT(`column`, '%Y') as `grouping_column`,
    ->        COUNT(`column`) as count
    -> FROM `tests`
    -> GROUP BY `grouping_column`;
+-----------------+-------+
| grouping_column | count |
+-----------------+-------+
| 2015            |  5007 |
| 2016            |  5003 |
+-----------------+-------+
2 rows in set (0.01 sec)

Aggregated by month

SELECT DATE_FORMAT(`column`, '%Y-%m') as `grouping_column`,
       COUNT(`column`) as count
FROM `tests`
GROUP BY `grouping_column`;
mysql> SELECT DATE_FORMAT(`column`, '%Y-%m') as `grouping_column`,
    ->        COUNT(`column`) as count
    -> FROM `tests`
    -> GROUP BY grouping_column;
+-----------------+-------+
| grouping_column | count |
+-----------------+-------+
| 2015-01         |   417 |
| 2015-02         |   434 |
| 2015-03         |   454 |
| 2015-04         |   422 |
| 2015-05         |   453 |
| 2015-06         |   388 |
| 2015-07         |   462 |
| 2015-08         |   403 |
| 2015-09         |   374 |
| 2015-10         |   403 |
| 2015-11         |   378 |
| 2015-12         |   419 |
| 2016-01         |   411 |
| 2016-02         |   399 |
| 2016-03         |   414 |
| 2016-04         |   426 |
| 2016-05         |   413 |
| 2016-06         |   406 |
| 2016-07         |   412 |
| 2016-08         |   432 |
| 2016-09         |   412 |
| 2016-10         |   427 |
| 2016-11         |   406 |
| 2016-12         |   445 |
+-----------------+-------+
24 rows in set (0.03 sec)

Aggregated by day of week

SELECT DATE_FORMAT(`column`, '%w') as `grouping_column`,
       COUNT(`column`) as count
FROM `tests`
GROUP BY `grouping_column`;
mysql> SELECT DATE_FORMAT(`column`, '%w') as `grouping_column`,
    ->        COUNT(`column`) as count
    -> FROM `tests`
    -> GROUP BY `grouping_column`;
+-----------------+-------+
| grouping_column | count |
+-----------------+-------+
| 0               |  1455 |
| 1               |  1400 |
| 2               |  1436 |
| 3               |  1374 |
| 4               |  1436 |
| 5               |  1474 |
| 6               |  1435 |
+-----------------+-------+
7 rows in set (0.02 sec)

0 is Sunday.

Aggregated by day

SELECT DATE_FORMAT(`column`, '%Y-%m-%d') as `grouping_column`,
       COUNT(`column`) as count
FROM `tests`
WHERE `column` >= "2015-02-01" AND `column` < "2015-03-01"
GROUP BY grouping_column;
mysql> SELECT DATE_FORMAT(`column`, '%Y-%m-%d') as `grouping_column`,
    ->        COUNT(`column`) as count
    -> FROM `tests`
    -> WHERE `column` >= "2015-02-01" AND `column` < "2015-03-01"
    -> GROUP BY grouping_column;
+-----------------+-------+
| grouping_column | count |
+-----------------+-------+
| 2015-02-01      |    18 |
| 2015-02-02      |    17 |
| 2015-02-03      |    14 |
| 2015-02-04      |    18 |
| 2015-02-05      |    12 |
| 2015-02-06      |    18 |
| 2015-02-07      |    15 |
| 2015-02-08      |    18 |
| 2015-02-09      |    17 |
| 2015-02-10      |    20 |
| 2015-02-11      |     9 |
| 2015-02-12      |    25 |
| 2015-02-13      |    14 |
| 2015-02-14      |    13 |
| 2015-02-15      |    19 |
| 2015-02-16      |    11 |
| 2015-02-17      |    14 |
| 2015-02-18      |    13 |
| 2015-02-19      |    17 |
| 2015-02-20      |    16 |
| 2015-02-21      |    15 |
| 2015-02-22      |    13 |
| 2015-02-23      |    13 |
| 2015-02-24      |    14 |
| 2015-02-25      |    13 |
| 2015-02-26      |    17 |
| 2015-02-27      |    19 |
| 2015-02-28      |    12 |
+-----------------+-------+
28 rows in set (0.00 sec)

Aggregated by time

SELECT DATE_FORMAT(`column`, '%H') as `grouping_column`,
       COUNT(`column`) as count
FROM `tests`
GROUP BY `grouping_column`;
mysql> SELECT DATE_FORMAT(`column`, '%H') as `grouping_column`,
    ->        COUNT(`column`) as count
    -> FROM `tests`
    -> GROUP BY `grouping_column`;
+-----------------+-------+
| grouping_column | count |
+-----------------+-------+
| 00              |   392 |
| 01              |   415 |
| 02              |   422 |
| 03              |   435 |
| 04              |   421 |
| 05              |   418 |
| 06              |   420 |
| 07              |   422 |
| 08              |   407 |
| 09              |   383 |
| 10              |   398 |
| 11              |   458 |
| 12              |   434 |
| 13              |   381 |
| 14              |   444 |
| 15              |   470 |
| 16              |   417 |
| 17              |   432 |
| 18              |   417 |
| 19              |   422 |
| 20              |   410 |
| 21              |   388 |
| 22              |   394 |
| 23              |   410 |
+-----------------+-------+
24 rows in set (0.01 sec)
MySQL
Let's share this post !

Related Articles

TOC