要获取所需的行，请先使用set
returning函数和横向联接。从那里，使用
CASE语句和日期算术提取相关值。
这是一个使您入门的示例：
with data as ( select id, start_date, end_date from (values (1, '2014-12-02 14:12:00+00'::timestamptz, '2014-12-03 06:45:00+00'::timestamptz), (2, '2014-12-05 15:25:00+00'::timestamptz, '2014-12-05 07:29:00+00'::timestamptz) ) as rows (id, start_date, end_date))select data.id, case days.d = date_trunc('day', data.start_date) when true then data.start_date else days.d end as start_date, case days.d = date_trunc('day', data.end_date) when true then data.end_date else days.d + interval '1 day' - interval '1 sec' end as end_datefrom datajoin generate_series( date_trunc('day', data.start_date), date_trunc('day', data.end_date), '1 day' ) as days (d) on days.d >= date_trunc('day', data.start_date) and days.d <= date_trunc('day', data.end_date) id | start_date | end_date ----+------------------------+------------------------ 1 | 2014-12-02 15:12:00+01 | 2014-12-02 23:59:59+01 1 | 2014-12-03 00:00:00+01 | 2014-12-03 07:45:00+01 2 | 2014-12-05 16:25:00+01 | 2014-12-05 08:29:00+01(3 rows)顺便说一句，根据您的工作，使用日期范围可能更有意义：
with data as ( select id, start_date, end_date from (values (1, '2014-12-02 14:12:00+00'::timestamptz, '2014-12-03 06:45:00+00'::timestamptz), (2, '2014-12-05 07:25:00+00'::timestamptz, '2014-12-05 15:29:00+00'::timestamptz) ) as rows (id, start_date, end_date))select data.id, tstzrange(data.start_date, data.end_date)from data; id |tstzrange----+----------------------------------------------------- 1 | ["2014-12-02 15:12:00+01","2014-12-03 07:45:00+01") 2 | ["2014-12-05 08:25:00+01","2014-12-05 16:29:00+01")(2 rows)
