MySQL datetime 월별 GROUP BY

1 개요[ | ]

MySQL datetime 월별 GROUP BY

문제상황: 워드프레스 글의 post_date 기준 월별 통계를 내고 싶다.
DATE_FORMAT(post_date,'%Y-%m') 또는 MID(post_date,1,7)로 GROUP BY

2 실습환경[ | ]

mysql> SELECT COUNT(*) FROM wp_posts;
+----------+
| COUNT(*) |
+----------+
|     4036 |
+----------+

mysql> SELECT ID, post_date FROM wp_posts ORDER BY post_date LIMIT 2;
+----+---------------------+
| ID | post_date           |
+----+---------------------+
|  1 | 2012-01-01 12:30:58 |
|  2 | 2012-01-01 14:05:09 |
+----+---------------------+

mysql> SELECT ID, post_date FROM wp_posts ORDER BY post_date DESC LIMIT 2;
+------+---------------------+
| ID   | post_date           |
+------+---------------------+
| 7313 | 2013-08-03 19:47:04 |
| 5354 | 2013-08-03 19:35:15 |
+------+---------------------+

→ 전체 포스팅 수는 4036건

→ 게시일시(post_date)는 2012-01-01 ~ 2013-08-03

3 방법 1: DATE_FORMAT() 함수[ | ]

mysql> SELECT DATE_FORMAT(post_date,'%Y-%m') m, COUNT(*) FROM wp_posts GROUP BY m;
+---------+----------+
| m       | COUNT(*) |
+---------+----------+
| 2012-01 |      128 |
| 2012-02 |      115 |
| 2012-03 |      121 |
... (생략)
| 2013-07 |      322 |
| 2013-08 |       32 |
+---------+----------+

4 방법 2: MID() 함수[ | ]

mysql> SELECT MID(post_date,1,7) m, COUNT(*) FROM wp_posts GROUP BY m;
+---------+----------+
| m       | COUNT(*) |
+---------+----------+
| 2012-01 |      128 |
| 2012-02 |      115 |
| 2012-03 |      121 |
... (생략)
| 2013-07 |      322 |
| 2013-08 |       32 |
+---------+----------+

5 방법 3: MONTH() 함수[ | ]

mysql> SELECT CONCAT(YEAR(post_date), '-', MONTH(post_date)) ym, COUNT(*) FROM wp_posts GROUP BY ym;
+---------+----------+
| ym      | COUNT(*) |
+---------+----------+
| 2012-1  |      128 |
| 2012-10 |      161 |
| 2012-11 |      159 |
| 2012-12 |      205 |
| 2012-2  |      115 |
| 2012-3  |      121 |
... (생략)
| 2013-7  |      322 |
| 2013-8  |       32 |
+---------+----------+

6 같이 보기[ | ]