Database 将数据拆分为月份(以Postgres为单位

Recommended Solution(h/t: Prosenjit Chongder):

使用101函数的解决方案更加紧凑.

SELECT TO_CHAR(GREATEST(y, c.start_at), 'DD/MM/YYYY') AS start_at,
       TO_CHAR(LEAST(y + INTERVAL '1 month' - INTERVAL '1 day', c.end_at), 'DD/MM/YYYY') AS end_at
FROM date_ranges c, 
     generate_series(date_trunc('month', c.start_at::timestamp), date_trunc('month', c.end_at::timestamp), INTERVAL '1 month') y
ORDER BY GREATEST(y, c.start_at), LEAST(y + INTERVAL '1 month' - INTERVAL '1 day', c.end_at);

Fiddle个

较旧的解决方案:

你需要一个递归Common table expression来达到你的目的.

WITH RECURSIVE date_split(start_at, end_at, current_start, current_end) AS (
  SELECT start_at, end_at,
         start_at, LEAST(end_at, (date_trunc('MONTH', start_at) + INTERVAL '1 MONTH' - INTERVAL '1 day')::DATE)
  FROM date_ranges
  WHERE start_at <= end_at
  UNION ALL
  SELECT start_at, end_at,
         (current_end + INTERVAL '1 day')::DATE,
         LEAST(end_at, (date_trunc('MONTH', current_end + INTERVAL '1 day') + INTERVAL '1 MONTH' - INTERVAL '1 day')::DATE)
  FROM date_split
  WHERE current_end < end_at
)
SELECT TO_CHAR(current_start, 'DD/MM/YYYY') AS start_at, TO_CHAR(current_end, 'DD/MM/YYYY') AS end_at
FROM date_split
ORDER BY current_start;

输出:

start_at    end_at
17/01/2023  31/01/2023
01/02/2023  28/02/2023
01/03/2023  15/03/2023

DBFiddle个