如何使SQLite实现与PostgreSQL一致的日期减月计算行为？

要让SQLite实现PostgreSQL这种「月末自动调整到上月最后一天」的减月逻辑，我们需要手动处理日期的边界情况——因为SQLite默认的date()函数是按字面量加减月份，遇到上月没有对应日期时会向前/向后溢出到下一个月，而PostgreSQL会直接锚定到上月的最后一天。

方法1：使用CASE表达式直接处理 #

你可以通过一段CASE逻辑来判断原日期的日部分是否超过上月的最大天数，从而决定返回值：

-- 替换其中的'2018-03-31'为你需要计算的日期
SELECT 
  CASE 
    -- 比较原日期的日和上月的最大天数
    WHEN CAST(strftime('%d', '2018-03-31') AS INTEGER) > CAST(strftime('%d', date('2018-03-31', 'start of month', '-1 month', '+1 month', '-1 day')) AS INTEGER)
    -- 如果超过，返回上月最后一天
    THEN date('2018-03-31', 'start of month', '-1 month', '+1 month', '-1 day')
    -- 否则返回常规减月后的日期
    ELSE date('2018-03-31', '-1 month')
  END AS adjusted_date;

解释一下关键部分：

• date('2018-03-31', 'start of month', '-1 month', '+1 month', '-1 day')：先拿到当前日期的月初，减1个月到上月初，再加1个月减1天，就得到了上月的最后一天。
• strftime('%d', ...)：提取日期的日部分，转成整数后做比较。

方法2：封装成自定义函数（更易用） #

如果需要多次使用这个逻辑，建议封装成SQLite自定义函数，调用起来更简洁：

-- 创建自定义函数
CREATE FUNCTION subtract_one_month(date_str TEXT) 
RETURNS TEXT 
AS $$
SELECT 
  CASE 
    WHEN CAST(strftime('%d', date_str) AS INTEGER) > CAST(strftime('%d', date(date_str, 'start of month', '-1 month', '+1 month', '-1 day')) AS INTEGER)
    THEN date(date_str, 'start of month', '-1 month', '+1 month', '-1 day')
    ELSE date(date_str, '-1 month')
  END;
$$ LANGUAGE SQL;

-- 调用示例
SELECT subtract_one_month('2018-03-28');  -- 返回 2018-02-28
SELECT subtract_one_month('2018-03-31');  -- 返回 2018-02-28
SELECT subtract_one_month('2018-04-30');  -- 返回 2018-03-30（4月30日减1月，3月有31天，无需调整）
SELECT subtract_one_month('2018-05-31');  -- 返回 2018-04-30（5月31日减1月，4月只有30天，锚定到4月最后一天）

这样无论输入的是3月28-31中的哪一天，都会返回2018-02-28，和PostgreSQL的行为完全一致。

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