[Rails] MySQL関数 TIMESTAMPADD で動的に日付計算して絞り込む

背景

• 複数の「住宅/Article」がある
  • 住宅にはそれぞれその建物が「完成した日/completion_date」が登録されている
• 住宅には加入している定期点検のサービスがそれぞれあり、
  • 加入しているサービスごとに点検時期（InspectionMonth.month）が複数設定されている
  • 例えば、
    • 住宅Aでは「１カ月点検」「３カ月点検」「１年点検」
    • 住宅Bでは「５年点検」「１０年点検」「５０年点検」
    • などなど

目的

• 「特定の日（通常は当日）」に点検時期を迎える住宅をSQL一発で絞り込みたい

環境

Ruby 2.7.2 / 2.7.8
Rails 6.0.3 / 6.1.7
MySQL 5.7

簡略化モデル

※実際にはもっといくつかのテーブルが関連しており、ArticleとInspectionMonthは直接繋がってはいないですが、その辺は省略しています。

# 住宅（戸建て・マンションの一室・テナント などなど）
#
# == Schema Information
#  id                          :bigint           not null, primary key
#  name(住宅名)                 :string(255)      default(""), not null
#  completion_date(完成日)      :date             not null
class Article < ApplicationRecord
  has_many :inspection_months
end

# 定期点検予定時期
#
# == Schema Information
#  id                                :bigint           not null, primary key
#  article_id                        :bigint           not null
#  title(点検名)                      :string(255)      default(""), not null
#  month(点検予定時期（カ月）)         :integer          not null
class InspectionMonth < ApplicationRecord
  belongs_to :article
end

TIMESTAMPADD

TIMESTAMPADDのリファレンスはこのようになっていました。

TIMESTAMPADD(unit,interval,datetime_expr)

整数式 interval を日付または日付時間式 datetime_expr に加算します。 interval の単位は、unit 引数で指定されます。この引数は、MICROSECOND (マイクロ秒)、SECOND、MINUTE、HOUR、DAY、WEEK、MONTH、QUARTER、YEAR 値のいずれかにする必要があります。

mysql> SELECT TIMESTAMPADD(MINUTE,1,'2003-01-02');
        -> '2003-01-02 00:01:00'
mysql> SELECT TIMESTAMPADD(WEEK,1,'2003-01-02');
        -> '2003-01-09'

これを使って以下のように書ければ、特定の日に点検時期となる住宅を絞り込めるはずです。

/* unit: 'MONTH' */
/* interval: InspectionMonth.month */
/* datetime_expr: Article.completion_date */
DATE(
  TIMESTAMPADD(
    MONTH,
    `inspection_months`.`month`,
    `articles`.`completion_date`
  )
) = TODAY()

実装

これをRailsで実装していきます。
Railsに用意されていないMySQL関数を使うため、Arel::Nodesを使っていきます。

class Article < ApplicationRecord
  scope :inspection_date_eq, -> (today = Time.zone.today) {
    .joins(:inspection_months)
    .where(
      # built sql:
      #   DATE(TIMESTAMPADD(MONTH, `inspection_months`.`month`, `articles`.`completion_date`)) = '2023-07-12'
      build_inspection_date.eq(today)
    )
  }

  def self.build_inspection_date
    # built sql:
    #   DATE(TIMESTAMPADD(MONTH, `inspection_months`.`month`, `articles`.`completion_date`))
    Arel::Nodes::NamedFunction.new('DATE', [
      Arel::Nodes::NamedFunction.new('TIMESTAMPADD', [
        Arel::Nodes::SqlLiteral.new('MONTH'),
        InspectionMonth.arel_table[:month],
        Article.arel_table[:completion_date]
      ])
    ])
  end

build_inspection_dateで一旦Arel::Nodesを組み立てておくことで、汎用的に利用できるようになります。
例えば以下のように再利用することができ、Arelのasを使ってカラムに別名をつけたりといったこともすっきり書くことが出来ます。

class Article < ApplicationRecord
  def self.select_inspection_dates(today = Time.zone.today)
    inspection_date_eq(today).select([
      Article.arel_table[:id].as('article_id'),
      Article.arel_table[:name],
      InspectionMonth.arel_table[:id].as('inspection_month_id'),
      InspectionMonth.arel_table[:title],
      InspectionMonth.arel_table[:month],
      build_inspection_date.as('inspection_date')
    ])
  end

完成

そして、inspection_date_eq のようなscopeを作っておけば目的の絞り込みが完成です。

検証

目的が達成されるか検証してみましょう。

閏年

日付計算で怪しいポイントはやはり閏年かと思いますので、まずその辺を確認していきます。

※計算結果がtodayと一致していれば想定通りとなります。
※カッコ書きしていないものはtoday = '2023-07-12'です。

閏年を跨がない１年間で日付がずれないか確認

MySQL > select DATE(TIMESTAMPADD(MONTH,12,'2022-07-12'));
+-------------------------------------------+
| DATE(TIMESTAMPADD(MONTH,12,'2022-07-12')) |
+-------------------------------------------+
| 2023-07-12                                |
+-------------------------------------------+

閏年を跨ぐ１年間で日付がずれないか確認（today = '2024-07-12）

MySQL > select DATE(TIMESTAMPADD(MONTH,12,'2023-07-12'));
+-------------------------------------------+
| DATE(TIMESTAMPADD(MONTH,12,'2023-07-12')) |
+-------------------------------------------+
| 2024-07-12                                |
+-------------------------------------------+

閏年を１０数回跨いでも日付がずれないか確認

MySQL > select DATE(TIMESTAMPADD(MONTH,12*50,'1973-07-12'));
+----------------------------------------------+
| DATE(TIMESTAMPADD(MONTH,12*50,'1973-07-12')) |
+----------------------------------------------+
| 2023-07-12                                   |
+----------------------------------------------+

月末

もし、建物完成日が1/31で、最初の点検時期が３カ月後だとすると、単純に足し算をすると4/31となってしまい存在しない日付となるかもしれません。その辺がどうなるか確認していきます。

完成日が31日で今日が月末の30日だった場合（today = '2023-04-30'）

MySQL > select DATE(TIMESTAMPADD(MONTH,3,'2023-01-31'));
+------------------------------------------+
| DATE(TIMESTAMPADD(MONTH,3,'2023-01-31')) |
+------------------------------------------+
| 2023-04-30                               |
+------------------------------------------+

ちゃんとできてますね。すごいですね。
どんどんいきましょう。

完成日が月末の30日で今日が月末ではない30日だった場合（today = '2023-07-30'）

MySQL > select DATE(TIMESTAMPADD(MONTH,3,'2023-04-30'));
+------------------------------------------+
| DATE(TIMESTAMPADD(MONTH,3,'2023-04-30')) |
+------------------------------------------+
| 2023-07-30                               |
+------------------------------------------+

これは当然何の問題もないですね。

完成日が閏年の２月末の場合（today = '2021-02-28'）

MySQL > select DATE(TIMESTAMPADD(MONTH,12,'2020-02-29'));
+-------------------------------------------+
| DATE(TIMESTAMPADD(MONTH,12,'2020-02-29')) |
+-------------------------------------------+
| 2021-02-28                                |
+-------------------------------------------+
MySQL > select DATE(TIMESTAMPADD(MONTH,12,'2020-02-28'));
+-------------------------------------------+
| DATE(TIMESTAMPADD(MONTH,12,'2020-02-28')) |
+-------------------------------------------+
| 2021-02-28                                |
+-------------------------------------------+

これも大丈夫そうですね。

検証結果

怪しそうな日付をいくつか検証してみましたが、TIMESTAMPADDの結果は必ず何かしらの存在する日付となって返ってくるようで最高でした。

これで例えば、1日1回のバッチ処理を設定して、その日に定期点検時期を迎える住宅を抽出してお知らせを送信したり出来そうですね。
また、月末の処理も理想通りだったため、毎日のバッチで２日連続同じ住宅にお知らせが届いたり、逆にまったく届かない住宅があったりといった心配もなさそうですね。

おわり

と、せっかくここまで検証しましたが、結局業務では使用しなかったので、メモも兼ねてお祭りに参加してみました。