PostgreSQL中日期差值的人性化格式化需求：按年/月/天层级展示

实现逻辑 #

基于需求示例，明确转换规则：

• 总天数小于30：直接输出「N days」
• 总天数≥365：按365天为1年计算年数，剩余天数单独显示，格式为「X year(s) and Y days」（无剩余天数时显示「X year(s)」）
• 总天数在30到364之间：按30天为1个月计算月数，剩余天数单独显示，格式为「X month(s) and Y days」（无剩余天数时显示「X month(s)」）

具体实现代码 #

创建自定义函数humanize_date_diff，接收两个日期参数，返回格式化后的字符串：

CREATE OR REPLACE FUNCTION humanize_date_diff(date1 DATE, date2 DATE)
RETURNS TEXT AS $$
DECLARE
    total_days INT := ABS(date1 - date2); -- 取绝对值，兼容任意日期顺序
    years INT;
    months INT;
    remaining_days INT;
BEGIN
    IF total_days < 30 THEN
        RETURN format('%s days', total_days);
    ELSIF total_days >= 365 THEN
        years := total_days / 365;
        remaining_days := total_days % 365;
        IF remaining_days > 0 THEN
            RETURN format('%s year%s and %s days', years, CASE WHEN years != 1 THEN 's' ELSE '' END, remaining_days);
        ELSE
            RETURN format('%s year%s', years, CASE WHEN years != 1 THEN 's' ELSE '' END);
        END IF;
    ELSE
        months := total_days / 30;
        remaining_days := total_days % 30;
        IF remaining_days > 0 THEN
            RETURN format('%s month%s and %s days', months, CASE WHEN months != 1 THEN 's' ELSE '' END, remaining_days);
        ELSE
            RETURN format('%s month%s', months, CASE WHEN months != 1 THEN 's' ELSE '' END);
        END IF;
    END IF;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

测试示例 #

调用函数验证需求场景：

1. 计算2023-04-05与2023-04-01的差值：

SELECT humanize_date_diff('2023-04-05', '2023-04-01');

返回：4 days

2. 计算2024-04-05与2023-04-01的差值：

SELECT humanize_date_diff('2024-04-05', '2023-04-01');

返回：1 year and 5 days

3. 计算2023-05-05与2023-04-01的差值：

SELECT humanize_date_diff('2023-05-05', '2023-04-01');

返回：1 month and 4 days

补充说明 #

• 函数用ABS处理日期顺序，无论传入的两个日期谁在前，都能得到正天数差值
• 自动处理单复数：年/月数为1时显示「year/month」，大于1时显示「years/months」
• 如果需要基于实际日期的精准年/月计算（而非固定365/30天），可以使用内置AGE函数实现精准版：

精准版实现代码 #

CREATE OR REPLACE FUNCTION humanize_date_diff_precise(date1 DATE, date2 DATE)
RETURNS TEXT AS $$
DECLARE
    age_interval INTERVAL := AGE(date1, date2);
    years INT := EXTRACT(YEAR FROM age_interval)::INT;
    months INT := EXTRACT(MONTH FROM age_interval)::INT;
    days INT := EXTRACT(DAY FROM age_interval)::INT;
    result_parts TEXT[] := '{}'::TEXT[];
BEGIN
    IF years > 0 THEN
        result_parts := array_append(result_parts, format('%s year%s', years, CASE WHEN years !=1 THEN 's' ELSE '' END));
    END IF;
    IF months > 0 THEN
        result_parts := array_append(result_parts, format('%s month%s', months, CASE WHEN months !=1 THEN 's' ELSE '' END));
    END IF;
    IF days > 0 OR (years = 0 AND months = 0) THEN
        result_parts := array_append(result_parts, format('%s day%s', ABS(days), CASE WHEN ABS(days) !=1 THEN 's' ELSE '' END));
    END IF;
    
    RETURN array_to_string(result_parts, ' and ');
END;
$$ LANGUAGE plpgsql IMMUTABLE;

这个版本会根据实际日期间隔计算年、月、天，比如闰年的2月29日到次年2月28日的差值会被识别为1年1天。

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