JUST FOR FUN

Twitter:@okwra Facebook:ayato.ookawara GitHub:@tearon4 other:@taiga006

日付・時間の文字列操作にはDATE_FORMATよりEXTRACTのほうが便利そう。

チームメンバーの書いたクエリで見覚えのない関数が使われいたので調査。

MySQL :: MySQL 5.6 リファレンスマニュアル :: 12.7 日付および時間関数

EXTRACT関数を使うとDATE値の結果から一部を抽出できる。
よく見るDATE_FORMAT関数を使うより文字列を解釈せずに必要な部分を抽出できるため早いらしい。

mysql> SELECT EXTRACT(HOUR_MINUTE FROM now());
+---------------------------------+
| EXTRACT(HOUR_MINUTE FROM now()) |
+---------------------------------+
|                            2353 |
+---------------------------------+
1 row in set (0.00 sec)

mysql> SELECT EXTRACT(DAY_MINUTE FROM '2012-04-13 21:10:41');
+------------------------------------------------+
| EXTRACT(DAY_MINUTE FROM '2012-04-13 21:10:41') |
+------------------------------------------------+
|                                         132110 |
+------------------------------------------------+
1 row in set (0.00 sec)

グループ化する際に特に便利。

qiita.com

qiita.com

以下、適当なテーブルを作って検証。

mysql> SELECT * FROM user LIMIT 1;
+----+-------+------+------+------------+---------------------+
| id | name  | age  | sex  | city       | created_at          |
+----+-------+------+------+------------+---------------------+
|  1 | illum |   43 | male | Wolffmouth | 2007-12-30 07:13:59 |
+----+-------+------+------+------------+---------------------+
1 row in set (0.00 sec)

mysql> SELECT sex,
       EXTRACT(YEAR
               FROM created_at) AS YEAR,
       COUNT(*) AS COUNT
       FROM USER
       GROUP BY sex,
         EXTRACT(YEAR
                 FROM created_at) HAVING COUNT(*) > 10
       ORDER BY extract(YEAR
                 FROM created_at);
+--------+------+-------+
| sex    | YEAR | COUNT |
+--------+------+-------+
| female | 1977 |    14 |
| male   | 1984 |    14 |
| female | 1984 |    13 |
| male   | 1990 |    11 |
| female | 1991 |    11 |
| female | 1998 |    11 |
| female | 2006 |    13 |
| male   | 2006 |    11 |
| male   | 2011 |    16 |
+--------+------+-------+
9 rows in set (0.00 sec)

最後にEXTRACTで使えるunit値を載せておく。

unit 要求される expr 書式
MICROSECOND MICROSECONDS
SECOND SECONDS
MINUTE MINUTES
HOUR HOURS
DAY DAYS
WEEK WEEKS
MONTH MONTHS
QUARTER QUARTERS
YEAR YEARS
SECOND_MICROSECOND 'SECONDS.MICROSECONDS'
MINUTE_MICROSECOND 'MINUTES:SECONDS.MICROSECONDS'
MINUTE_SECOND 'MINUTES:SECONDS'
HOUR_MICROSECOND 'HOURS:MINUTES:SECONDS.MICROSECONDS'
HOUR_SECOND 'HOURS:MINUTES:SECONDS'
HOUR_MINUTE 'HOURS:MINUTES'
DAY_MICROSECOND 'DAYS HOURS:MINUTES:SECONDS.MICROSECONDS'
DAY_SECOND 'DAYS HOURS:MINUTES:SECONDS'
DAY_MINUTE 'DAYS HOURS:MINUTES'
DAY_HOUR 'DAYS HOURS'
YEAR_MONTH 'YEARS-MONTHS'