Prestoで1ヶ月後の時刻を求める時に気をつけること

普段の業務で利用しているPresto (treasuredata) で１ヶ月後の日付を求める機会があり、
こちらのドキュイメントになる、date_add という関数の挙動をテストした時に見つけた挙動のメモです。

6.13. Date and Time Functions and Operators

こちら、日付単位のデータのn日後やn日前を求める時には何も問題ないのですが、
nヶ月後を求める時に少し不思議な動きがありました。

まず前提として、元のデータの日付部分が、1〜28日の場合は、何も問題がありません。
5月16日の3ヶ月後は 8月16日ですし、
2ヶ月前は 3月16日です。


SELECT
    DATE_ADD('month', 3, timestamp '2019-05-16 12:00:00'),  -- 2019-08-16 12:00:00.000
    DATE_ADD('month', -2, timestamp '2019-05-16 12:00:00') -- 2019-03-16 12:00:00.000

問題は、月によって日数が違う点です。
例えば、8月は31日までありますが、9月は30日までしかありません。
そのため、8月31日の1ヶ月後をPrestoのDATE_ADDで算出すると、9月30日になります。
ちなみに、9月30日の1ヶ月前は8月30日です。
要するに、8月31日に1ヶ月足して1ヶ月引くと8月30日になり、元に戻らない。


SELECT
    DATE_ADD('month', 1, timestamp '2019-08-30'), -- 2019-09-30 00:00:00.000
    DATE_ADD('month', 1, timestamp '2019-08-31'),  -- 2019-09-30 00:00:00.000
    DATE_ADD('month', -1, timestamp '2019-09-30'),  -- 2019-08-30 00:00:00.000
    DATE_ADD('month', -1, timestamp '2019-10-01'),  -- 2019-09-01 00:00:00.000
    DATE_ADD('month', -1, DATE_ADD('month', 1, timestamp '2019-08-31')) -- 2019-08-30 00:00:00.000

こういう挙動を嫌って、いつもDATE_ADDは利用せず、60*60*24*30秒足したり引いたりするクエリを書いていたのですが、
30日と1ヶ月は厳密には違うので、その時に応じてよく考えて方法を選ぶ必要があります。

そして、問題はもう一点あります。
それは時刻まで含めて計算した時に、時刻の前後関係が前後することです。
8月30日と8月31日の１ヶ月後はどちらも9月30日と算出されますが、
これが時刻も含データの場合、時刻部分は1ヶ月足す前の値から変化しません。

要するに
(1)8月30日20時の１ヶ月後は9月30日20時で、
(2)8月31日7時の１ヶ月後は9月30日7時です。
元の時間は当然、(1)の方が前なのに、(1)と(2)の1ヶ月後の時刻は(2)の1ヶ月後の方が前になります。


SELECT
    DATE_ADD('month', 1, timestamp '2019-08-30 20:00:00'), -- 2019-09-30 20:00:00.000
    DATE_ADD('month', 1, timestamp '2019-08-31 07:00:00'),  -- 2019-09-30 07:00:00.000
    timestamp '2019-08-30 20:00:00' < timestamp '2019-08-31 07:00:00', -- true
    DATE_ADD('month', 1, timestamp '2019-08-30 20:00:00') < DATE_ADD('month', 1, timestamp '2019-08-31 07:00:00') --false

日付単位で行った時の不等号が等号になるのはまだ許容範囲かもしれませんが、
不等号の反転はちょっと困る。

幸い、nヶ月間以内の判定を時刻まで考慮して厳密に行う場面は少ない(これまでほぼなかった)ので
問題になることは少ないのですが、注意が必要です。
(timestampに変換する前に時刻を切り捨てるなどの処理を入れた方がいい。)

以上をまとめると、Prestoの DATE_ADDで月単位(month)の演算をする時の注意は次の3つです。

異なる日付の±nヶ月後が同じ日付になることがある
ある日付のnヶ月後のnヶ月前が元の日付と異なることがある
２つの時間のnヶ月後を計算すると時間の前後関係が入れ替わることがある

とくに2月がからむと最悪で、
１月28,29,30,31日の1ヶ月後は全部2月28日になります。

コメントを残す コメントをキャンセル

コメントを残すコメントをキャンセル