目次
MariaDBバージョンアップでtime型に対するdate_formatの出力が変わっていた件
MariaDBを10.1から10.3にあげたシステムで、うまく動かない機能がありました。
その原因が、実に意外なものだったので書いておきたいと思います。
date_format・time_format
SQL文で使える組み込み関数で「date_format」・「time_format」があります。
今回はその動きの「非互換」に関する話題です。
この関数は日付・時刻をフォーマットして出力します。
例えば、DateTime型の「t_col」というカラムに「2022-10-04 22:23:00」という日時がはいっていた場合に
DATE_FORMAT(t_col, '%H:%i:%s')
とすると「22:23:00」と時刻部分をフォーマットして取り出せます。
ここで使っている「%H」という指定が、今回問題になりました。
%Hのオプションは「00-23」の範囲で時間を返す・・はずなのに?
%Hのオプションについて、上記のドキュメントを見ると。
%H Hour with 2 digits between 00-23.(00~23の2桁で構成される時間)
とあります。
つまり。
例えば「26:00」のように24:00を超える時刻であっても、%Hを指定すると「02:00」のように時間の部分は「00-23」の範囲で出力されると理解できます。
実際、MariaDB10.1で動かすと、そうなっていました。
ところが。
MariaDB10.3にバージョンアップして動作がおかしくなったプログラムを調査してみると「%H」指定なのに「26:00」みたいに24時間を超えた時間が返されていて、それが原因でIF文の分岐が間違ったところにとんでいました。
なんで「00-23の範囲」で時間が返ってくる仕様なのに、24時を超える時間が返されるようになっているのか?・・というのが問題になりました。
Time型だけが動きに差があるみたいです
調べてみると、次のことがわかりました。
- 日付・時刻の型全部に対して問題があるわけではない
- そもそも「26:00」みたいに24時を超えた時間が返る方が正かもしれない
です。
まず、日付・時刻の型全部に対して問題があるわけではない・・です。
MariaDBの日付・時刻型にはいくつか種類があり、date_formatは、そのいずれの型も引数にとることができます。
その型とは
- Date
- DateTime
- TimeStamp
- Time
などです。
このうち「%H」で「26:00」みたいな24時を超える時間を返すのはTime型のみです。
よく考えれば当たり前で、日付・時刻の両方を管理する型なら24時を超えると次の日になるので、そもそも24時を超える時刻を保持する必要がありません。
対して、Time型は時刻だけを保持し有効範囲は「'-838:59:59.999999' から '838:59:59.999999'」なわけですから。
そして、「26:00」みたいに24時を超えた時間が返る方が正かもしれないと考えた理由は、MySQLのTime型ドキュメントを見たからです。
そこには「MySQL では、TIME 値の取り出しと表示は 'HH:MM:SS' 形式 (時間の部分の値が大きい場合は 'HHH:MM:SS' 形式) で行われます」とも書いてありました。
なので、MySQL5.6でも同じことをすれば「26:00」のように24時を超えた時間が返されていた可能性があり(環境がないので未確認ですけど)、そうだとしたら、それとコンパチブルであるはずの、MariaDB10.1の関数が異なる動きをしていたことになります。
なので。
MariaDB10.1の時はTime型でも「00-23」の範囲で時間が出力されていた。
というのが実は間違いだったのかもしれないなと、僕は思っています。
戻り値をそろえるにはCASTすればいいだけなんだけどね
まあ。
原因はともあれ、10.1と10.3で動きが異なっていて、それで問題が発生しているのは事実なので、なんとかしないといけません。
こういう場合おおまかに2通りのアプローチがあります。
- DBのバージョンによって出力が変わることを考慮して後続ロジックを修正する
- SQL文を修正して10..3でも10.1の時と同じ値が返るようにする
です。
普通に考えて、前者はけっこう面倒くさいです。
DBのバージョンチェックまでプログラムで考慮しないといけませんし、かといって、どちらの値がか返ってきても大丈夫にする・・というのは、その処理の目的によっては、なかなかに骨が折れるだろうなということは、すぐわかります。
たぶん。
後者の「SQL文を修正して10..3でも10.1の時と同じ値が返るようにする」として、後続ロジックは一切さわらないほうが楽でしょうね。
これを実現する手っ取り早い方法は、date_formatでTIme型が引数になっている部分をDateTime型にでもCASTしてやることだと思います。
例えば、上記の例で
DATE_FORMAT(t_col, '%H:%i:%s')
この「t_col」がTime型だとすれば
DATE_FORMAT(CAST(t_col AS DATETIME), '%H:%i:%s')
のようにSQLを修正するわけです。
これで、MariaDBのバージョンにかかわらず時間は「00-23」の範囲で返されることが保証はされます。
とはいえ、
修正箇所が一か所であることはなく、こんなシンプルにソース中にSQLを書いているケースはあまりない(SQLを自動生成とかしてる場合もありますし)でしょうから、簡単な作業ではなく、結局のところ、SQLとソース側のロジックとどっちを直すのがコスパがいいのか・・みたいな比較検討しないといけない・・ことにはなりますけどね。
それにしても。
いろいろありますね。
ではでは。