skip to Main Content

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

2

Answers


  1. 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
    ...
    
    
    Login or Signup to reply.
  2. --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.

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search