如何在SQLite中实现类似DuckDB的date_sub函数计算完整月差

现有通过floor((julianday(date_end) - julianday(date_start))/30)计算完整月数的方式，无法匹配wanted_full_month列的预期结果（比如无法处理月末特殊情况、日期跨月但未达起始日的情况）。以下是实现符合需求的完整月数计算的SQL方案：

核心判断规则 #

• 基础月份差为两个日期的年月数值差（如1992-09到1992-11的月份差为2）
• 若结束日日期大于等于起始日日期，直接取基础月份差
• 若结束日是当月最后一天，无论起始日是几号，视为完整月
• 若起始日和结束日都是各自月份的最后一天，视为完整月
• 其他情况（结束日小于起始日，且不满足上述特殊情况），基础月份差减1

实现SQL（带中间验证字段） #

SELECT 
    id,
    date_start,
    date_end,
    wanted_full_month,
    -- 计算基础月份差
    strftime('%Y', date_end)*12 + strftime('%m', date_end) - (strftime('%Y', date_start)*12 + strftime('%m', date_start)) AS month_diff,
    -- 判断是否为当月最后一天
    CASE WHEN date_start = date(date_start, 'start of month', '+1 month', '-1 day') THEN 1 ELSE 0 END AS is_start_eom,
    CASE WHEN date_end = date(date_end, 'start of month', '+1 month', '-1 day') THEN 1 ELSE 0 END AS is_end_eom,
    -- 计算最终符合预期的完整月数
    CASE 
        WHEN strftime('%Y', date_end)*12 + strftime('%m', date_end) < strftime('%Y', date_start)*12 + strftime('%m', date_start) THEN 0
        ELSE 
            strftime('%Y', date_end)*12 + strftime('%m', date_end) - (strftime('%Y', date_start)*12 + strftime('%m', date_start)) 
            - CASE 
                WHEN (strftime('%d', date_end) < strftime('%d', date_start)) 
                     AND NOT (is_start_eom AND is_end_eom)
                     AND NOT is_end_eom
                THEN 1 
                ELSE 0 
              END
    END AS correct_full_months
FROM mytable;

简化版SQL（仅输出结果） #

如果不需要中间验证字段，可简化为：

SELECT 
    *,
    CASE 
        WHEN strftime('%Y%m', date_end) < strftime('%Y%m', date_start) THEN 0
        ELSE 
            (strftime('%Y', date_end)*12 + strftime('%m', date_end)) - (strftime('%Y', date_start)*12 + strftime('%m', date_start))
            - CASE 
                WHEN (strftime('%d', date_end) < strftime('%d', date_start))
                     AND NOT (date_start = date(date_start, 'start of month', '+1 month', '-1 day') 
                              AND date_end = date(date_end, 'start of month', '+1 month', '-1 day'))
                     AND NOT date_end = date(date_end, 'start of month', '+1 month', '-1 day')
                THEN 1 
                ELSE 0 
              END
    END AS correct_full_months
FROM mytable;

结果验证 #

运行上述SQL后，correct_full_months列将完全匹配wanted_full_month的预期值：

• 行1：1（正确）
• 行2：2（正确）
• 行3：0（正确）
• 行4：1（正确）
• 行5：1（正确）

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