SQLite周递归日期查询：如何获取指定日期对应的周重复events记录

前置背景 #

表结构与约束 #

• 不可修改现有events表结构
• 表字段说明：
  • kind：事件类型
  • starts_at：事件开始时间，存储为ISO格式字符串
  • ends_at：事件结束时间，存储为ISO格式字符串
  • weekly_recurring：布尔值，为true代表事件每周同一时段重复

需求说明 #

查询指定日期当日的指定类型事件时，可正确匹配对应周重复记录。例如插入2020-01-01 09:00的周重复opening事件后，查询2020-01-08的opening类型事件，需返回该条记录。

现有实现代码 #

插入记录代码

await knexClient("events").insert([
  {
    kind: "opening",
    starts_at: new Date("2020-01-01 09:00").toISOString(),
    ends_at: new Date("2020-01-01 09:30").toISOString(),
    weekly_recurring: true,
  }
])

原有查询代码（存在问题）

knexClient('events')
  .where('kind', 'opening')
  .whereRaw("(julianday(?) - julianday(starts_at)) % 7 = 0", [date.toISOString()])
  .select('starts_at', 'ends_at');

问题分析 #

原有查询逻辑存在三个核心问题：

1. 没有截断时间的时分秒部分，若starts_at的时间与查询参数的时间不一致，会导致julianday计算的差值不为整数，模7结果永远不等于0
2. 未区分周重复与非周重复事件，非周重复事件会被错误匹配所有间隔7天倍数的日期
3. 未校验查询日期是否晚于等于事件首次开始时间，会匹配到还未到首次生效时间的周重复事件

调整后正确实现 #

// date为要查询的目标日期对象，例如 new Date('2020-01-08')
const queryDateStr = date.toISOString();

const matchedEvents = await knexClient('events')
  .where('kind', 'opening')
  .where(function () {
    // 匹配非周重复的普通事件：开始日期与查询日期完全一致
    this.where('weekly_recurring', false)
      .whereRaw("date(starts_at) = date(?)", [queryDateStr])
    // 匹配周重复事件：查询日期晚于等于首次开始日期，且日期差为7的整数倍
    .orWhere('weekly_recurring', true)
      .whereRaw("date(?) >= date(starts_at)", [queryDateStr])
      .whereRaw("(julianday(date(?)) - julianday(date(starts_at))) % 7 = 0", [queryDateStr])
  })
  .select('starts_at', 'ends_at', 'weekly_recurring');

关键修改说明 #

• 使用SQLite内置date()函数提取时间的日期部分，消除时分秒对日期差计算的影响
• 拆分周重复和非周重复事件的匹配逻辑，避免普通事件被错误匹配
• 增加周重复事件的生效时间校验，排除未到首次生效时间的记录

内容的提问来源于stack exchange，提问作者Nainpo