<< 目次へ
更新日：2013.4.29
小技集・豆知識
日付データへの変換
MySQLで「日付」の操作を行うことがよくあります。例えば、顧客の購買履歴のデータから「基準日からの直近購買日までの日数」などを計算することがあります。MySQLには以下に示す4つのデータ型が用意されています。
データマイニング業務において、実質的に取り扱うことが多いのは「DATE型」のみといってもよいでしょう。データとしてDATETIME型として保持しているケースもありますが、時間単位（HH:MI:SS）でデータを保持していても、使用するのはほぼ日付単位（YYY-MM-DD）での情報だからです。
場合によっては文字型として日付のデータを保持している（データテーブルに読み込まれている）ケースがあります。その場合には「文字型を日付型（DATE型やDATETIME型）に変換」する必要がでてきます。その方法を紹介していきましょう。
■CAST関数による日付型への変換
最もてっとり早く日付型へ変換する方法がCAST関数を用いることである。CAST関数は非常に便利な関数で、使い勝手もよいのでおススメである。
#### CAST関数の使い方 CAST('日付を表す文字列' as DATE) # DATEをDATETIMEとすることも可能 ## 20090823という文字列をDATE型に変換 select cast('20090823' as date); mysql> select cast('20090823' as date); +--------------------------+ | cast('20090823' as date) | +--------------------------+ | 2009-08-23 | +--------------------------+ ## 20090823という文字列をDATETIME型に変換 select cast('20090823' as datetime); mysql> select cast('20090823' as datetime); +------------------------------+ | cast('20090823' as datetime) | +------------------------------+ | 2009-08-23 00:00:00 | ←【注】時間単位(HH:MI:SS)の情報は全て0になる +------------------------------+ ## '2009-08-23 11:20:00'をDATETIME型に変換 select cast('2009-08-23 11:20:00' as datetime); mysql> select cast('2009-08-23 11:20:00' as datetime); +-----------------------------------------+ | cast('2009-08-23 11:20:00' as datetime) | +-----------------------------------------+ | 2009-08-23 11:20:00 | +-----------------------------------------+ ## '2009-08-23 11:20:00'をDATE型に変換 select cast('2009-08-23 11:20:00' as date); mysql> select cast('2009-08-23 11:20:00' as date); +-------------------------------------+ | cast('2009-08-23 11:20:00' as date) | +-------------------------------------+ | 2009-08-23 | ←【注】時間単位(HH:MI:SS)の情報は切り捨てられる +-------------------------------------+
なお余談だが、CAST関数を用いる際に指定できる「日付を表す文字列」にはいくつかのバリエーションに対応できるようになっている。ただしDATE型へ変換する際とDATETIME型へ変換する際とで、若干の動作が異なる点に注意すべきである。
#### 【DATE型への変換の場合】 ## 固定桁の文字列はOK mysql> select cast('20090823' as date); +--------------------------+ | cast('20090823' as date) | +--------------------------+ | 2009-08-23 | +--------------------------+ ## ハイフンつなぎもOK mysql> select cast('2009-08-23' as date); +----------------------------+ | cast('2009-08-23' as date) | +----------------------------+ | 2009-08-23 | +----------------------------+ ## スラッシュつなぎもOK mysql> select cast('2009/08/23' as date); +----------------------------+ | cast('2009/08/23' as date) | +----------------------------+ | 2009-08-23 | +----------------------------+ ## カンマつなぎもOK mysql> select cast('2009,08,23' as date); +----------------------------+ | cast('2009,08,23' as date) | +----------------------------+ | 2009-08-23 | +----------------------------+ ## 年月日つがぎはNG（ただし後に紹介するSTR_TO_DATE関数なら可） mysql> select cast('2009年08月23日' as date); +--------------------------------+ | cast('2009年08月23日' as date) | +--------------------------------+ | NULL | +--------------------------------+ #### 【DATETIME型への変換の場合】 # DATETIME型への変換も同様にできるが、以下の例はNG mysql> select cast('20090823 11:20:00' as datetime); +---------------------------------------+ | cast('20090823 11:20:00' as datetime) | +---------------------------------------+ | NULL | ←年月日が固定桁だとNG +---------------------------------------+
■STR_TO_DATE関数による日付型への変換
関数としての機能はほぼ同じであるが、STR_TO_DATE関数を使うことでより細かい変換に対応することができる。
#### STR_TO_DATEの基本的な使い方 ## 固定桁の文字列をDATE型に変換 select str_to_date('20090823', '%Y%m%d'); mysql> select str_to_date('20090823', '%Y%m%d'); +-----------------------------------+ | str_to_date('20090823', '%Y%m%d') | ←%Y:Year, %m:month, %d:day +-----------------------------------+ | 2009-08-23 | ←cast('20090823' as date)と同じ +-----------------------------------+ ## 【注意】大文字と小文字は区別されている mysql> select str_to_date('20090823', '%Y%M%d'); +-----------------------------------+ | str_to_date('20090823', '%Y%M%d') | +-----------------------------------+ | NULL | ←%Mではなく、%mにしないとダメ +-----------------------------------+ ## 上記以外にもハイフン(-)，スラッシュ(/)，カンマ(,)に対応している ## STR_TO_DATE()ではさらに以下のようなケースにも対応できる select str_to_date('2009年8月23日', '%Y年%m月%d日'); mysql> select str_to_date('2009年8月23日', '%Y年%m月%d日'); +----------------------------------------------+ | str_to_date('2009年8月23日', '%Y年%m月%d日') | +----------------------------------------------+ | 2009-08-23 | ←年・月・日で区切られた文字列に対応 +----------------------------------------------+ select str_to_date('August 23,2009', '%M %d,%Y'); mysql> select str_to_date('August 23,2009', '%M %d,%Y'); +-------------------------------------------+ | str_to_date('August 23,2009', '%M %d,%Y') | +-------------------------------------------+ | 2009-08-23 | ←英表記の文字列にも対応 +-------------------------------------------+ ## 【注意】大文字と小文字は区別されている mysql> select str_to_date('August 23,2009', '%m %d,%Y'); +-------------------------------------------+ | str_to_date('August 23,2009', '%m %d,%Y') | +-------------------------------------------+ | NULL | ←%mではなく、%Mにしないとダメ +-------------------------------------------+
上記の例において「%m」と「%M」はMySQLで指定子と呼ばれるものであある。%mは「月の数字」を表すもので、%Mは「月名」を指定するものである。そのためstr_to_date('20090823', '%Y%M%d')は間違いである（ここでは[08]という月の数字であるので%mにしなければならない）。一方でstr_to_date('August 23,2009', '%m %d,%Y')も間違いである（ここでは[August]という月名であるので%Mにしなければならない）。
以下にMySQLの指定子の一覧を示す。
http://dev.mysql.com/doc/refman/5.1/ja/date-and-time-functions.htmlより引用。
指定子 解説 %a 簡略曜日名 (Sun..Sat) %b 簡略月名 (Jan..Dec) %c 月、数字 (0..12) %D 英語の接尾辞を持つ日にち (0th, 1st, 2nd, 3rd, …) %d 日にち、数字 (00..31) %e 日にち、数字 (0..31) %f マイクロ秒 (000000..999999) %H 時間 (00..23) %h 時間 (01..12) %I 時間 (01..12) %i 分、数字 (00..59) %j 通日 (001..366) %k 時間 (0..23) %l 時間 (1..12) %M 月名 (January..December) %m 月、数字 (00..12) %p AM または PM %r 時間、12 時間単位 (hh:mm:ss に AM または PM が続く ) %S 秒 (00..59) %s 秒 (00..59) %T 時間、24 時間単位 (hh:mm:ss) %U 週 (00..53) 、週の開始は日曜日 %u 週 (00..53)、週の開始は月曜日 %V 週 (01..53) 、週の開始は日曜日で、%X と使用 %v 週 (01..53) 、週の開始は月曜日で、%x と使用 %W 曜日名 (Sunday..Saturday) %w 曜日 (0=Sunday..6=Saturday) %X 年間の週、週の始まりは日曜日、週、数字、4 桁 ; %V と使用 %x 年間の週、週の始まりは月曜日、数字、4 桁、%v と使用 %Y 年、数字、4 桁 %y 年、数字 ( 2 桁 ) %% リテラル ‘%’ 文字 %x x 、上記にないすべての ‘x’
