clear markdown compare help best fiddles feedback dbanow.uk
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2805438 fiddles created (40825 in the last week).

select version();
version
PostgreSQL 12.8 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.4.1 20200928 (Red Hat 8.4.1-1), 64-bit
 hidden batch(es)


CREATE TABLE assign_dates ( p_date DATE );
 hidden batch(es)


INSERT INTO assign_dates (p_date) values('29-08-2021'::DATE),('29-09-2021'::DATE);
2 rows affected
 hidden batch(es)


CREATE INDEX a_d_ix ON assign_dates (p_date);
 hidden batch(es)


SELECT * FROM GENERATE_SERIES ( (SELECT MIN(p_date) FROM assign_dates), (SELECT MAX(p_date) FROM assign_dates), '1 DAY' ) AS tab (the_date);
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
2021-09-03 00:00:00+01
2021-09-04 00:00:00+01
2021-09-05 00:00:00+01
2021-09-06 00:00:00+01
2021-09-07 00:00:00+01
2021-09-08 00:00:00+01
2021-09-09 00:00:00+01
2021-09-10 00:00:00+01
2021-09-11 00:00:00+01
2021-09-12 00:00:00+01
2021-09-13 00:00:00+01
2021-09-14 00:00:00+01
2021-09-15 00:00:00+01
2021-09-16 00:00:00+01
2021-09-17 00:00:00+01
2021-09-18 00:00:00+01
2021-09-19 00:00:00+01
2021-09-20 00:00:00+01
2021-09-21 00:00:00+01
2021-09-22 00:00:00+01
2021-09-23 00:00:00+01
2021-09-24 00:00:00+01
2021-09-25 00:00:00+01
2021-09-26 00:00:00+01
2021-09-27 00:00:00+01
2021-09-28 00:00:00+01
2021-09-29 00:00:00+01
 hidden batch(es)


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;
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
2021-09-03 00:00:00+01 5 Friday
2021-09-06 00:00:00+01 1 Monday
2021-09-07 00:00:00+01 2 Tuesday
2021-09-08 00:00:00+01 3 Wednesday
2021-09-09 00:00:00+01 4 Thursday
2021-09-10 00:00:00+01 5 Friday
2021-09-13 00:00:00+01 1 Monday
2021-09-14 00:00:00+01 2 Tuesday
2021-09-15 00:00:00+01 3 Wednesday
2021-09-16 00:00:00+01 4 Thursday
2021-09-17 00:00:00+01 5 Friday
2021-09-20 00:00:00+01 1 Monday
2021-09-21 00:00:00+01 2 Tuesday
2021-09-22 00:00:00+01 3 Wednesday
2021-09-23 00:00:00+01 4 Thursday
2021-09-24 00:00:00+01 5 Friday
2021-09-27 00:00:00+01 1 Monday
2021-09-28 00:00:00+01 2 Tuesday
2021-09-29 00:00:00+01 3 Wednesday
 hidden batch(es)


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;
date
2021-01-01 00:00:00
2021-01-02 00:00:00
2021-01-03 00:00:00
2021-01-04 00:00:00
2021-01-05 00:00:00
2021-01-08 00:00:00
2021-01-09 00:00:00
2021-01-10 00:00:00
2021-01-11 00:00:00
2021-01-12 00:00:00
2021-01-15 00:00:00
2021-01-16 00:00:00
2021-01-17 00:00:00
2021-01-18 00:00:00
2021-01-19 00:00:00
2021-01-22 00:00:00
2021-01-23 00:00:00
2021-01-24 00:00:00
2021-01-25 00:00:00
2021-01-26 00:00:00
2021-01-29 00:00:00
2021-01-30 00:00:00
2021-01-31 00:00:00
2021-02-01 00:00:00
2021-02-02 00:00:00
 hidden batch(es)


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);
the_date rn
2021-08-29 00:00:00+01 1
2021-08-30 00:00:00+01 2
2021-08-31 00:00:00+01 3
2021-09-01 00:00:00+01 4
2021-09-02 00:00:00+01 5
2021-09-03 00:00:00+01 6
2021-09-04 00:00:00+01 7
2021-09-05 00:00:00+01 8
2021-09-06 00:00:00+01 9
2021-09-07 00:00:00+01 10
2021-09-08 00:00:00+01 11
2021-09-09 00:00:00+01 12
2021-09-10 00:00:00+01 13
2021-09-11 00:00:00+01 14
2021-09-12 00:00:00+01 15
2021-09-13 00:00:00+01 16
2021-09-14 00:00:00+01 17
2021-09-15 00:00:00+01 18
2021-09-16 00:00:00+01 19
2021-09-17 00:00:00+01 20
2021-09-18 00:00:00+01 21
2021-09-19 00:00:00+01 22
2021-09-20 00:00:00+01 23
2021-09-21 00:00:00+01 24
2021-09-22 00:00:00+01 25
2021-09-23 00:00:00+01 26
2021-09-24 00:00:00+01 27
2021-09-25 00:00:00+01 28
2021-09-26 00:00:00+01 29
2021-09-27 00:00:00+01 30
2021-09-28 00:00:00+01 31
2021-09-29 00:00:00+01 32
 hidden batch(es)


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;
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
2021-09-07 00:00:00+01 10 3 Tuesday
2021-09-08 00:00:00+01 11 4 Wednesday
2021-09-09 00:00:00+01 12 5 Thursday
2021-09-12 00:00:00+01 15 1 Sunday
2021-09-13 00:00:00+01 16 2 Monday
2021-09-14 00:00:00+01 17 3 Tuesday
2021-09-15 00:00:00+01 18 4 Wednesday
2021-09-16 00:00:00+01 19 5 Thursday
2021-09-19 00:00:00+01 22 1 Sunday
2021-09-20 00:00:00+01 23 2 Monday
2021-09-21 00:00:00+01 24 3 Tuesday
2021-09-22 00:00:00+01 25 4 Wednesday
2021-09-23 00:00:00+01 26 5 Thursday
2021-09-26 00:00:00+01 29 1 Sunday
2021-09-27 00:00:00+01 30 2 Monday
2021-09-28 00:00:00+01 31 3 Tuesday
2021-09-29 00:00:00+01 32 4 Wednesday
 hidden batch(es)