在PostgreSQL 11中，我试图获得一个周末时间范围。周五17:00至周日17:00。
到目前为止，我可以通过获得一个工作日
select * from generate_series(date '2021-01-01',date '2021-12-31',interval '1' day) as t(dt) where extract (dow from dt) between 1 and 5;
然而，从开始(星期五17:00(到结束(星期日17:00(，我在创建2列时遇到了问题。
预期输出应该是这样的：
start stop
2022-10-07 17:00 2022-10-09 17:00
2022-10-14 17:00 2022-10-16 17:00
2022-10-21 17:00 2022-10-23 17:00
获取周五的17:00和周日的17:00之间的一系列所有小时。
SELECT
*
FROM
generate_series(timestamp '2021-01-01', timestamp '2021-12-31', interval '1' hour) AS t (dt)
WHERE
extract(dow FROM dt) IN (5, 6, 0)
AND CASE WHEN extract(dow FROM dt) = 5 THEN
extract(hour FROM dt) >= 17
WHEN extract(dow FROM dt) = 0 THEN
extract(hour FROM dt) <= 17
ELSE
extract(hour FROM dt) IS NOT NULL
END;
更新
获取两个时间戳，表示在一系列日期上每个周期Friday 17:00到Sunday 17:00的开始和停止。
SELECT
dt + '17:00'::time as start, (dt + '17:00'::time) + '2 days'::interval as stop
FROM
generate_series(date '2022-01-01', date '2022-12-31', interval '1' day) AS t (dt)
WHERE
extract(dow FROM dt) = 5
;
start | stop
-------------------------+-------------------------
01/07/2022 17:00:00 PST | 01/09/2022 17:00:00 PST
01/14/2022 17:00:00 PST | 01/16/2022 17:00:00 PST
01/21/2022 17:00:00 PST | 01/23/2022 17:00:00 PST
01/28/2022 17:00:00 PST | 01/30/2022 17:00:00 PST
02/04/2022 17:00:00 PST | 02/06/2022 17:00:00 PST
02/11/2022 17:00:00 PST | 02/13/2022 17:00:00 PST
02/18/2022 17:00:00 PST | 02/20/2022 17:00:00 PST
02/25/2022 17:00:00 PST | 02/27/2022 17:00:00 PST
03/04/2022 17:00:00 PST | 03/06/2022 17:00:00 PST
03/11/2022 17:00:00 PST | 03/13/2022 17:00:00 PDT
03/18/2022 17:00:00 PDT | 03/20/2022 17:00:00 PDT
03/25/2022 17:00:00 PDT | 03/27/2022 17:00:00 PDT
...
--timestamptz type.
SELECT
(day + interval '17:30') AS start,
(day + interval '17:30' + interval '2 days') AS
END
FROM
generate_series(date '2022-10-01', date '2022-12-31', interval '1' day) _ (day)
WHERE
EXTRACT(ISODOW FROM day) = 5;
--timestamp type.
SELECT
(day + interval '17:30')::timestamp AS start,
(day + interval '17:30' + interval '2 days')::timestamp AS
END
FROM
generate_series(date '2022-10-01', date '2022-12-31', interval '1' day) _ (day)
WHERE
EXTRACT(ISODOW FROM day) = 5;
我确实检查了日历，它有效。