Exclude weekends in PostgresSQL

Question

I am trying to find number of slides based on duedate but only want to include business days (exclude weekends).

Here is what I have so far for my criteria:

Due in 1 Day:

sum(CASE when orders.duedate::date > current_date and orders.duedate::date - 1 = current_date then nullif(orders.slides,0) END

) as "Slides (1 Day)",

sum(CASE when orders.duedate::date > current_date and orders.duedate::date - 2 = current_date then nullif(orders.slides,0) END

) as "Slides (2 Day)"

and so on.

On a Monday, this code accomplishes what I want as it shows what's due in the next few days but on a Friday, for slides that are due in 1 day, I want it to show slides due on Monday and for due in 2 days, I want it to show Tuesday instead of Saturday and Sunday.

Any help here would be greatly appreciated!

1) This, `orders.duedate::date > current_date and orders.duedate::date - 1 = current_date` seems redundant. If `orders.duedate::date - 1 = current_date` then `orders.duedate::date` has to be `>` then `current_date`. 2) You will need to use [Extract](https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT) and `dow` or `isodow` and nested `CASE` to use different offset for Friday. — Adrian Klaver, Nov 07 '22 at 16:21
Does this answer your question? [Counting number of weekdays between two dates](https://stackoverflow.com/questions/48954983/counting-number-of-weekdays-between-two-dates) — user3738870, Nov 07 '22 at 17:59

score 0 · Answer 1 · answered Nov 07 '22 at 21:03

Using CTE.

CURRENT_DATE example:

CREATE TABLE public.slide_test (
    id integer,
    slides integer,
    order_date date
);


INSERT INTO
   slide_test
VALUES 
    (1,2,'2022-11-07'), (2,3,'2022-11-08'),(3,1,'2022-11-09'),
    (4,5,'2022-11-10'), (5,3,'2022-11-11'),(6,8, '2022-11-14'),
    (7,7, '2022-11-15');

select * from slide_test ;
 id | slides | order_date 
----+--------+------------
  1 |      2 | 11/07/2022
  2 |      3 | 11/08/2022
  3 |      1 | 11/09/2022
  4 |      5 | 11/10/2022
  5 |      3 | 11/11/2022
  6 |      8 | 11/14/2022
  7 |      7 | 11/15/2022

SELECT CURRENT_DATE;
current_date 
--------------
 11/07/2022

WITH not_fri AS (
    SELECT
        CASE WHEN order_date - '1 day'::interval = CURRENT_DATE AND
            extract(dow from CURRENT_DATE) != 5 THEN
            sum(nullif (slides, 0))
        END AS "1 Day",
        CASE WHEN order_date - '2 day'::interval = CURRENT_DATE AND
            extract(dow from CURRENT_DATE) != 5  THEN
            sum(nullif (slides, 0))
        END AS "2 Day"
    FROM
        slide_test
    GROUP BY
        order_date
),
fri AS (
    SELECT
        CASE WHEN order_date - '3 day'::interval = CURRENT_DATE AND
            extract(dow from CURRENT_DATE) = 5 THEN
            sum(nullif (slides, 0))
        END AS "1 Day",
        CASE WHEN order_date - '4 day'::interval = CURRENT_DATE AND
            extract(dow from CURRENT_DATE) = 5 THEN
            sum(nullif (slides, 0))
        END AS "2 Day"
    FROM
        slide_test
    GROUP BY
        order_date
)
SELECT
    *
FROM
   not_fri
UNION
SELECT
    *
FROM
    fri;


 1 Day | 2 Day 
-------+-------
  NULL |  NULL
  NULL |     1
     3 |  NULL

Using a Friday date:

WITH not_fri AS (
    SELECT
        CASE WHEN order_date - '1 day'::interval = '11/11/2022'::date AND
            extract(dow from '11/11/2022'::date) != 5 THEN
            sum(nullif (slides, 0))
        END AS "1 Day",
        CASE WHEN order_date - '2 day'::interval = '11/11/2022'::date AND
            extract(dow from '11/11/2022'::date) != 5  THEN
            sum(nullif (slides, 0))
        END AS "2 Day"
    FROM
        slide_test
    GROUP BY
        order_date
),
fri AS (
    SELECT
        CASE WHEN order_date - '3 day'::interval = '11/11/2022'::date AND
            extract(dow from '11/11/2022'::date) = 5 THEN
            sum(nullif (slides, 0))
        END AS "1 Day",
        CASE WHEN order_date - '4 day'::interval = '11/11/2022'::date AND
            extract(dow from '11/11/2022'::date) = 5 THEN
            sum(nullif (slides, 0))
        END AS "2 Day"
    FROM
        slide_test
    GROUP BY
        order_date
)
SELECT
    *
FROM
   not_fri
UNION
SELECT
    *
FROM
    fri;

 1 Day | 2 Day 
-------+-------
  NULL |  NULL
     8 |  NULL
  NULL |     7

Exclude weekends in PostgresSQL

1 Answers1