PostgreSQL creating timestamp ranges-CodePudding

In PostgreSQL 11, I am trying to get a weekend time range. From 17:00 Friday to Sunday 17:00.

So far I am able to get a working day by doing

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;

However, I am have trouble creating 2 columns from start (17:00 Friday) to finish (17:00 Sunday).

Expected output should be something like this:

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

CodePudding user response：

To get a series of all hours between 17:00 on Friday and 17:00 on Sunday.

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;

UPDATE

Get two timestamps that represent start and stop of each period Friday 17:00 to Sunday 17:00 over a range of dates.

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
...

CodePudding user response：

--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;

I do checked the calendar, it works.