チームメンバーの書いたクエリで見覚えのない関数が使われいたので調査。
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)
グループ化する際に特に便利。
以下、適当なテーブルを作って検証。
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' |