目次
日付リテラルを使った日付の比較
SQLで、日付リテラルを使って日付の比較をしているものを、たまに見かけます。
正直、やばいなと思います。
エラーにはならないですけど、バグになりやすい部分だからです。
今回はそのへんを整理します。
エラーにならず一見うまくいったように見えたケース
お題にするのは、超シンプルなこういうSQLです。
select case when '2018-09-01' > '2018-08-31' then 0 else 1 end;
「'2018-09-01'」が「'2018-08-31'」より大きければ「0」を表示し、でなければ「1」を表示するという処理です。
上記を実行すると、当然のように結果として「0」を表示します。
では。
間違った結果がかえったケース１
日付文字列を与えているわけですが、大小比較はできているので、日付文字列から日付に変換して処理できているんだろうなと考えました。
それなら、日付文字列の形式が前と後ろで変わっても、正しい日付文字列であるかぎり問題なくできるはずです。
で、試してみました。
select case when '2018-09-01' > '2018/08/31' then 0 else 1 end;
違いは、 「'2018-08-31'」が「'2018/08/31'」に変わっただけです。
これも当たり前のように結果として「0」が表示される。
そう思います。
ところが、どっこい。
結果で表示されるのは「1」です。
うーん。
間違った結果がかえったケース２
では、デリミタが同じで、月日のところで頭ゼロを省略しただけの以下のようなケースではどうかと試すと。
select case when '2018-09-01' > '2018-8-31' then 0 else 1 end;
結果としては「1」が表示されます。
駄目ですね。
間違った結果がかえったケース３
本当にそうなのか？と思って、こんなSQLにしてみると
select case when '2018-09-01' > '18/08/31' then 0 else 1 end;
前後の形式が全く違いますが、でも、結果は正しく「0」を表示します。
おお！この形式なら大丈夫なのか？と思ったら
select case when '2018-09-01' > '18/09/31' then 0 else 1 end;
みたいに、「1」が表示してほしい条件に変更しても、「0」が返ります。
やっぱり日付として正しく処理されてはないみたいです。
日付リテラルに関するMariDBのドキュメント
DATEリテラルについては、MariaDBのドキュメントに記述があります。
引用します。
元が英語なので、下にGoogle翻訳してます。
A DATE string is a string in one of the following formats: 'YYYY-MM-DD' or 'YY-MM-DD'. Note that any punctuation character can be used as delimiter. All delimiters must consist of 1 character. Different delimiters can be used in the same string. Delimiters are optional (but if one delimiter is used, all delimiters must be used).A DATE literal can also be an integer, in one of the following formats: YYYYMMDD or YYMMDD.
DATE文字列は、 'YYYY-MM-DD'または 'YY-MM-DD'のいずれかの形式の文字列です。任意の句読点文字を区切り文字として使用できることに注意してください。すべての区切り文字は1文字で構成する必要があります。 同じ文字列で異なるデリミタを使用することができます。デリミタはオプションです（しかし、1つのデリミタを使用する場合は、すべてのデリミタを使用する必要があります）。DATEリテラルは、YYYYMMDDまたはYYMMDDのいずれかの形式の整数でもかまいません。
うーん。
but if one delimiter is used, all delimiters must be used
のあたりかなとも思いますが、「これだ！」というほどの説明はありません。
日付リテラルで日付の比較はしない方が良い
はっきりしているのは、日付リテラルを使った日付の比較は、できないことはないみたいだけど、十分な注意が必要だということです。
嫌なのは。
エラーで落ちないことです。
何となく正しく動いているように見えていながら、しれっと間違った結果を返すというのが、一番ダメージがきついです。
横着しないで、ちゃんと日付型に返還して比較しろということですね。
以下みたいに。
select case when str_to_date('2018-09-01','%Y-%m-%d') > str_to_date('2018-08-31','%Y-%m-%d') then 0 else 1 end;
そうでなければ、日付形式を徹底的に統一する。
例えば「'2018/08/31'」を表示に使うなら、すべてをそれに統一して、フォーマットが混在しないように、きちんとやる。
やっぱ、基本が大事ですよね。
