Postgres自定义函数,格式化日期 年-周,年-非自然月

52万条数据耗时3秒左右

1.年-周

-- 年-周
create or replace function year_week(datetime timestamp) returns varchar
    language plpgsql
as
$$
declare
    year int;
    week int;
    day  int;
begin
    year := extract(year from datetime);
    week := extract(week from datetime);
    day := extract(day from datetime);

    if day < 6 and week > 50 then
        return concat(year - 1, '-', week);
    elseif day > 360 and week < 2 then
        return concat(year + 1, '-', week);
    else
        return concat(year, '-', week);
    end if;
end;
$$;
alter function year_week(datetime timestamp) owner to postgres;

-- 年-周
create or replace function year_week(datetime date) returns varchar
    language plpgsql
as
$$
declare
    year int;
    week int;
    day  int;
begin
    year := extract(year from datetime);
    week := extract(week from datetime);
    day := extract(day from datetime);

    if day < 6 and week > 50 then
        return concat(year - 1, '-', week);
    elseif day > 360 and week < 2 then
        return concat(year + 1, '-', week);
    else
        return concat(year, '-', week);
    end if;
end;
$$;
alter function year_week(datetime date) owner to postgres;

2.年-非自然月 (timestamp 字段类型)

如果本月的X号开始,则month_start_day 为 正数,例如每个月的起始日是3号,则 month_start_day = 3
如果上月的X号开始,则month_start_day 为 负数,例如每个月的起始日是上月25号,则 month_start_day = -25

-- 自定义月开始日的 年-月
create or replace function year_month(datetime date, month_start_day int) returns character varying
    language plpgsql
as
$$
declare
    year  int;
    month int;
    day   int;
begin
    if month_start_day isnull or month_start_day = 0 or month_start_day = 1 then
        return to_char(datetime, 'YYYY-MM');
    end if;

    year := extract(year from datetime);
    month := extract(month from datetime);
    day := extract(day from datetime);

    if month_start_day < 0 and day < -month_start_day then
        return concat(year, '-', to_char(month, 'FM00'));
    elseif month_start_day < 0 and day >= -month_start_day then
        return to_char(date_trunc('MONTH', datetime) + interval '1 MONTH', 'YYYY-MM');
    elseif month_start_day > 1 and day < month_start_day then
        return to_char(date_trunc('MONTH', datetime) - interval '1 MONTH', 'YYYY-MM');
    elseif month_start_day > 1 and day >= month_start_day then
        return concat(year, '-', to_char(month, 'FM00'));
    else
        return to_char(datetime, 'YYYY-MM');
    end if;
end;
$$;

alter function year_month(date, int) owner to postgres;

-- 自定义月开始日的 年-月
create or replace function year_month(datetime timestamp, month_start_day int) returns character varying
    language plpgsql
as
$$
declare
    year  int;
    month int;
    day   int;
begin
    if month_start_day isnull or month_start_day = 0 or month_start_day = 1 then
        return to_char(datetime, 'YYYY-MM');
    end if;

    year := extract(year from datetime);
    month := extract(month from datetime);
    day := extract(day from datetime);

    if month_start_day < 0 and day < -month_start_day then
        return concat(year, '-', to_char(month, 'FM00'));
    elseif month_start_day < 0 and day >= -month_start_day then
        return to_char(date_trunc('MONTH', datetime) + interval '1 MONTH', 'YYYY-MM');
    elseif month_start_day > 1 and day < month_start_day then
        return to_char(date_trunc('MONTH', datetime) - interval '1 MONTH', 'YYYY-MM');
    elseif month_start_day > 1 and day >= month_start_day then
        return concat(year, '-', to_char(month, 'FM00'));
    else
        return to_char(datetime, 'YYYY-MM');
    end if;
end;
$$;

alter function year_month(date, int) owner to postgres;