PostgreSQL实现每月自动计算截至上月最新价格的动态日期配置

直接用PostgreSQL内置的日期函数替代静态日期字符串，就能实现每月自动计算截至上月的数据，不用手动修改日期参数。

核心日期函数说明 #

• 上月第一天：date_trunc('month', current_date - interval '1 month')
  比如当前是3月，会返回2023-02-01；当前是4月，返回2023-03-01。
• 上月最后一天：date_trunc('month', current_date) - interval '1 day'
  自动处理闰年，比如2024年2月会返回2024-02-29，非闰年返回2023-02-28。
• 当月第一天：date_trunc('month', current_date)
  用来作为effective_date <的条件，确保只取到上月月底的数据。

修改后的完整查询 #

（补全了你原查询中缺失的子查询筛选和闭合语句）

SELECT 
    item,
    cost_price,
    effective_date
FROM 
    join_purchase_table
WHERE effective_date BETWEEN date_trunc('month', current_date - interval '1 month') 
                        AND (date_trunc('month', current_date) - interval '1 day')
UNION 
(
    SELECT 
        item,
        cost_price,
        effective_date
    FROM (
        SELECT 
            item,
            cost_price,
            effective_date,
            row_number() over(partition by item order by effective_date desc) AS rn
        FROM 
            join_purchase_table
        WHERE 
            effective_date < date_trunc('month', current_date)
    ) sub_query
    WHERE rn = 1
)

补充说明 #

如果你的PostgreSQL版本是11及以上，也可以用last_day(current_date - interval '1 month')替代date_trunc('month', current_date) - interval '1 day'来获取上月最后一天，写法更简洁，但date_trunc的方式兼容性更强，不需要依赖额外函数。

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