0
create table ASSIGNDATES (
P_DATE date );

insert into ASSIGNDATES (P_DATE)
values('29-08-2021'),('29-09-2021');

I have a table with value of date with one month difference, here I want to generate date series.

Requirement is it should print 5 continuous days then it should leave 2 days then again 5 days.

For example I want output like this:

 29-08-21
 30-08-21
 31-08-21
 01-09-21
 02-09-21 //2days gap 
 05-09-21 
 06-09-21
 07-09-21
 08-09-21
 09-09-21 //2days gap
 12-09-21
 13-09-21
 14-09-21
 15-09-21
 16-09-21 //2days gap
 19-09-21
 20-09-21

Please kindly help me solving this with answers

6
  • These `5 continuous days' are from Monday to Friday?
    – McNets
    Sep 29 at 10:16
  • Generate, enumerate, select needed.
    – Akina
    Sep 29 at 10:30
  • @McNets yes assume it as any 5days as working days Sep 29 at 11:57
  • @Akina can i get any solution codes, because i'm new to this Sep 29 at 11:58
  • 1
    Do you need in (1) working days within the period (Mon-Fri, even when the period starts not from Mon); (2) 5 days then 2 days gap (for any day of the week of the first day of the period)?
    – Akina
    Sep 29 at 12:09
1
with CTE as ( SELECT *, ROW_NUMBER() OVER () rn
              FROM generate_series('2021-01-01'::timestamp, '2021-02-02', '1 day') gs("date") )
SELECT "date"
FROM cte
WHERE rn % 7 BETWEEN 1 AND 5

https://dbfiddle.uk/?rdbms=postgres_12&fiddle=aca098faf658ca953960e702b5cf9997

0
1

You can create a function that generates a series of dates, avoiding these two days. Keep in mind that these two days could be a parameter of the function.

CREATE FUNCTION genDates(t1 date, t2 date)
RETURNS setof date
AS $$
  WITH dt AS
  (
    SELECT d::date, extract(dow from d) dow
    FROM generate_series(t1::timestamp, t2::timestamp, '1 day') AS gs(d)
  )
  SELECT d 
  FROM dt 
  WHERE dow <> extract(dow from t1+5) and dow <> extract(dow from t1+6)
$$
LANGUAGE sql
IMMUTABLE;

Now you can use it in this way:

SELECT * FROM genDates('2021-08-29'::date, '2021-09-29'::date)

And this is the result:

gendates
2021-08-29
2021-08-30
2021-08-31
2021-09-01
2021-09-02
2021-09-05
2021-09-06
2021-09-07
2021-09-08
2021-09-09
2021-09-12
2021-09-13
2021-09-14
2021-09-15
2021-09-16
2021-09-19
2021-09-20
2021-09-21
2021-09-22
2021-09-23
2021-09-26
2021-09-27
2021-09-28
2021-09-29

db<>fiddle here

1

You will need to do something like this (all of the code below is available on the fiddle here):

SELECT
  *
FROM
  GENERATE_SERIES
  (
    (SELECT MIN(p_date) FROM assign_dates),
    (SELECT MAX(p_date) FROM assign_dates),
    '1 DAY'
  ) AS tab (the_date);

Result:

     the_date
2021-08-29 00:00:00+01
2021-08-30 00:00:00+01
2021-08-31 00:00:00+01
2021-09-01 00:00:00+01
2021-09-02 00:00:00+01
...
... snipped for brevity
...

and then you will want to do something like the following:

SELECT the_date, EXTRACT(ISODOW FROM the_date), TO_CHAR(the_date, 'Day')
FROM
(
  SELECT
    *
  FROM
    GENERATE_SERIES
    (
      (SELECT MIN(p_date) FROM assign_dates),
      (SELECT MAX(p_date) FROM assign_dates),
      '1 DAY'
    ) AS tab01 (the_date)
) AS tab02
WHERE EXTRACT(ISODOW FROM the_date) BETWEEN 1 AND 5
ORDER BY the_date;

Result:

         the_date      date_part      to_char
2021-08-30 00:00:00+01     1           Monday   
2021-08-31 00:00:00+01     2          Tuesday  
2021-09-01 00:00:00+01     3        Wednesday
2021-09-02 00:00:00+01     4         Thursday 
...
... snipped for brevity
...

You can vary your WHERE clause as appropriate for the days you wish to eliminate - and you won't want both the date_part and to_char results. I found this, this and this helpful.

EDIT:

It was pointed out to me that the 2 day gap had to be 5 days after the start of the period, so I've adjusted the SQL accordingly (see fiddle here):

SELECT 
  the_date, rn, rn % 7 AS mod7 -- you can leave out the rn's for the final query!
  , TO_CHAR(the_date, 'Day')
FROM
(
  SELECT
    the_date, ROW_NUMBER() OVER (ORDER BY the_date) AS rn
  FROM
    GENERATE_SERIES
    (
      (SELECT MIN(p_date) FROM assign_dates),
      (SELECT MAX(p_date) FROM assign_dates),
      '1 DAY'
    ) AS tab (the_date)
) AS tab_01
WHERE rn % 7 BETWEEN 1 AND 5
ORDER BY the_date;

Result:

         the_date      rn   mod7      to_char
2021-08-29 00:00:00+01  1      1       Sunday   
2021-08-30 00:00:00+01  2      2       Monday   
2021-08-31 00:00:00+01  3      3      Tuesday  
2021-09-01 00:00:00+01  4      4    Wednesday
2021-09-02 00:00:00+01  5      5     Thursday 
2021-09-05 00:00:00+01  8      1       Sunday   
2021-09-06 00:00:00+01  9      2       Monday   
...
... snipped for brevity
...

So, we can see that the result starts on Sunday, runs 5 days till Thursday and then skips Friday and Saturday and starts again on the Sunday - as requested. Thanks to @Akina for pointing that out! Also, thanks to @ypercube for pointing out a potential ORDER BY issue with the ROW_NUMBER() function.

7
  • OP needs "5 days then 2 days gap (for any day of the week of the first day of the period)".
    – Akina
    Sep 29 at 12:54
  • @Akina - thanks for that! Upvoted your answer! :-) Sep 29 at 13:20
  • OVER () ? That should be OVER (the_date) Sep 29 at 22:03
  • @ypercubeᵀᴹ No, it shouldn't. If you look at rn, it goes from 1-5, 6 & 7 are removed by the WHERE and then you have 8-9 (rest snipped) which is what we want! Here is what you get if partitioning by the_date - rn is 1 for everything! Sep 29 at 22:17
  • 1
    @ypercubeᵀᴹ Corrected. Your point about a more complex query is well made. I'm blue in the face telling people that a query should always have an ORDER BY - Guess I should heed my own advice! :-) Sep 30 at 3:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.