[TOSDI] 日付時刻型のカラムを扱う場合のポイント

Talend Open Studio for Data Integration関連エントリです。
今回は日付時刻型のカラムを含むテーブルのデータを取得する際の注意についてです。
主にMySQLとの関係について説明しています。他RDBMSについては各仕様に置き換えてお考えください。

.

文中の「TOSDI」は「Talend Open Studio for Data Integration」を略して記述したものです。

標準の動き

TOSDIで日付型や日付時刻型の列を含むテーブルをリポジトリに登録する際、RDBMSやデータ型の細かい違いを問わずデフォルトでは「日付パターン」が「"dd-MM-yyyy"」になっています。

TOSDIではテーブルから取得した各値(属性値)をDB上の列定義から最適なJavaの型(クラス)の変数に格納しようとします。
上の画像では左から5列目の「Type」の欄は取得後にJavaで扱う際の型になります。これはこの画面で手動で変更できます。
Javaの"Date"は日付時刻を扱うクラスで、ミリ秒まで格納できる精度を持ちます。
http://docs.oracle.com/javase/jp/6/api/java/util/Date.html
「日付パターン」はこのクラスから値を取り出して表示する際やSQL文を生成する際に使用されます。
（なお、上の画像で、「BIGINT UNSIGNED」のフィールドを"String"クラスに格納しようとする理由については次回解説する予定です。）

挿入可能なパターン

これをこのまま別のテーブルの日付型の列に挿入しようとした場合、"dd-MM-yyyy"のパターンの文字列で挿入しようとします。
これを受け入れられるRDBMSならばよいのですが、すくなくともMySQLでは無効です。
SQLモード次第で、エラーとなるか、すべて"0000-00-00"で挿入されてしまいます。
(MySQLの)DATE型であれば、"yyyy-MM-dd"などに変更する必要があります。

MySQLで日付型(DATE型)の列へのINSERT時に受け入れられる文字列は"yyyy-MM-dd"か"yy-MM-dd"です。
（区切り文字は半角記号ならほとんどの文字を受け入れる。区切り文字ナシも可。）
http://dev.mysql.com/doc/refman/5.6/en/date-and-time-types.html

時刻情報の損失

取得する元のカラムが日付時刻型で時刻まで格納している場合、時刻の部分が失われます。
MySQLのDATETIME型やTIMESTAMP型（やOracleのDATE型）の場合は時刻まで格納されている可能性があるので、秒までの時刻を取得できる日付パターンに変更すべきです。
この場合、"yyyy-MM-dd HH:mm:ss"となります。

日付パターンの選択

なお、TOSDIのGUIでは、日付パターンの列にカーソルを合わせてCtrl+Spaceでいくつかのパターンから選択できるようになります。

"yyyy-MM-dd HH:mm:ss"が選択肢にないのですが、MySQLの場合、"yyyy-MM-dd'T'HH:mm:ss"でも問題ありません。
(選択肢に"yyyy-MM-dd"がないのが謎です。)

"yyyy-MM-dd'T'HH:mm:ss"の"T"は実際に「T」という半角英字で、この書式はISO8601の定義に基づいているものと思われます。
MySQLのマニュアルでは特に説明されていませんが、エラーにならず挿入可能です。「T」以外の(記号ではない)文字は挿入時にエラーになります。
またISO8601のサブセットにあたるRFC3339で定義されている"Z"をつけ"yyyy-MM-dd'T'HH:mm:ss'Z'"にすると挿入時にエラーになります。(例:2013-10-28T11:51:45Z)

ISO8601:http://ja.wikipedia.org/wiki/ISO_8601
RFC3339:http://suika.fam.cx/~wakaba/wiki/sw/n/RFC%203339
W3CDTF:http://www.w3.org/TR/NOTE-datetime (参考)
MySQL5.6 DATETIME:http://dev.mysql.com/doc/refman/5.6/en/datetime.html

なお、MySQL5.6.4以降でDATETIMEにミリ秒まで格納している先進的なあなたは"yyyy-MM-dd'T'HH:mm:ss.SSS"で取り出してください。
ただし、マイクロ秒まで格納している場合、つまりDATETIME(6)などfsp(fractional seconds part)を4以上にしている場合は、JavaのDateクラスの制限によりこの方法では取り出せません。
これについての具体的なテクニックは次回紹介したいと思います。

デフォルトの挙動変更について

なお、この日付パターンのデフォルトをアプリで設定できたらと思うんですが、残念ながらその機能はありません。
数年前から要望は上がっているのですが、実装はされていません。
https://jira.talendforge.org/browse/TDI-5927

各RDBMSの各データ型ごとに最適な日付パターンがプリセットされているのがよいと思うんですけどね。


Talend Open Studio for Data Integration関連エントリまとめ

.