SQLite 按指定X分钟间隔查询消息表的分组起始时间

核心逻辑 #

以全表最早的消息时间为固定锚点，按照指定的X分钟间隔划分连续时间桶，筛选出所有包含至少1条消息的桶，返回每个桶的起始时间，完全匹配样例的输出规则。

可直接执行的查询模板 #

将语句中的X替换为目标时间间隔（单位：分钟），messages替换为你实际使用的表名即可直接运行：

WITH base AS (
    -- 计算全局最早消息的Unix时间戳，作为分桶固定锚点
    SELECT strftime('%s', MIN(date)) AS t0 FROM messages
)
SELECT DISTINCT
    -- 计算当前消息所属时间桶的起始时间
    datetime(
        b.t0,
        '+' || ( (strftime('%s', m.date) - b.t0) / (X * 60) ) * X * 60 || ' seconds'
    ) AS group_start_date
FROM messages m, base b
ORDER BY group_start_date ASC;

样例匹配验证 #

使用你给出的测试消息集：[2020-11-30 02:00:01, 2020-11-30 02:00:11, 2020-11-30 02:01:51, 2020-11-30 02:01:40, 2020-11-30 02:00:32]

• 当X=1（1分钟间隔）：
  全局锚点为2020-11-30 02:00:01，02:00:01、02:00:11、02:00:32与锚点的时间差为0s、10s、31s，除以60秒的间隔得到整数0，属于0号桶；02:01:40、02:01:51与锚点的时间差为99s、110s，除以60秒的间隔得到整数1，属于1号桶，桶起始时间为锚点+60秒即2020-11-30 02:01:01。最终输出结果与预期的[2020-11-30 02:00:01, 2020-11-30 02:01:01]完全一致。
• 当X=5（5分钟间隔）：
  所有消息与锚点的最大时间差为110s，除以300秒的间隔得到整数0，所有消息均属于0号桶，最终仅返回[2020-11-30 02:00:01]，与预期完全一致。

注意事项 #

• 方案依赖SQLite内置时间函数，要求date字段为SQLite默认的YYYY-MM-DD HH:MM:SS标准字符串格式，如果是自定义格式的时间值，需要调整strftime的格式参数适配
• 由于锚点取全局最早时间，所有消息与锚点的时间差必然为非负值，SQLite的整数除法取整逻辑不会出现偏差
• 数据量较大时，建议为date字段建立索引，可大幅提升锚点计算与分桶查询的性能

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