date_diffで日付要素を加味して月数の差を求めたいときのメモ

概要

date_diff関数は、2つの日付型に対して、指定した粒度での差を返す。
ただし、その際、指定した粒度より細かい要素は切り捨てる形で評価する。

そのため、日付間の月数を取りたいが日数が加味されない、年数を取る際に月数が加味されないといった性質が有り、実用上問題のある場合がある。

やり方

基準となる日付を切り捨て、その差分をもう一方の日付にも同様に差し引く。

例

select
    x, y,
    date_diff(y, x, month),
    date_diff(date_sub(y, interval date_diff(x, date_trunc(x, month), day) day), date_trunc(x, month), month)
from
    unnest([
        struct(date('2022-05-01') as x, date('2022-05-29') as y),
        struct(date('2022-05-01') as x, date('2022-06-01') as y),
        struct(date('2022-05-29') as x, date('2022-06-01') as y),
        struct(date('2022-05-29') as x, date('2022-06-01') as y),
        struct(date('2022-05-29') as x, date('2022-06-29') as y),
        struct(date('2022-05-29') as x, date('2022-06-30') as y)
    ])