推荐方案（h/t：* Prosenjit Chongder *）：

使用 * generate_series * 函数的解决方案要紧凑得多。

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的

以下操作将生成所述结果：

WITH parms AS (SELECT '2023-01-17'::date AS start_at, '2023-03-15'::date AS end_at)
SELECT greatest(parms.start_at, g.month_start)::date AS start_at,
       least(parms.end_at, g.month_start + interval '1' MONTH - interval '1' DAY)::date AS end_at
FROM parms
CROSS JOIN LATERAL generate_series(date_trunc('month',parms.start_at), parms.end_at, interval '1' MONTH) g(month_start)
ORDER BY start_at;

字符串
结果如下：
| 开始于|结束于|
| --|--|
| 2023-01-17 2023-01-17 2023-01-17| 2023-01-31 -01- 01 - 01 - 01|
| 2023-02-01 2023-02-01 2023-02-01| 2023-02-28 2023-02-28 2023-02-28|
| 2023-03-01 2023-03-01 2023-03-01| 2023-03-15 2023-03-15 2023-03-15|