Numbering dates from Sunday to Saturday

Question

Does anyone have a easy solution to make a numbering from sunday to saturday and generate the dates in PostgreSQL(version 11).I have the below solution but it is limited to only 5 weeks and i need something that is flexible.

I have dates as a column in my source table, i want those dates to be numbered from saturday to sunday like below.

Current Query

 WITH CTE AS
    (                
    SELECT  1 as rno,generate_series(  date_trunc('week', current_date)::date - 1
    ,   date_trunc('week', current_date)::date + 5
    , interval  '1 day') current_week
    )
    ,CTE_1 AS
    (
    SELECT  rno,current_week FROM CTE   
    UNION
    select 2,dt::date d from generate_series( (SELECT MIN(current_week)::DATE FROM CTE)- interval '7 days', (SELECT MIN(current_week)::DATE FROM CTE)- interval '1 days', interval '1 days') dt
    )
    ,CTE_2 AS
    (
    SELECT  rno,current_week FROM CTE_1
    UNION
    select 3,dt::date d from generate_series( (SELECT MIN(current_week)::DATE FROM CTE_1)- interval '7 days', (SELECT MIN(current_week)::DATE FROM CTE_1)- interval '1 days', interval '1 days') dt
    )
    ,CTE_3 AS
    (
    SELECT  rno,current_week FROM CTE_2
    UNION
    select 4,dt::date d from generate_series( (SELECT MIN(current_week)::DATE FROM CTE_2)- interval '7 days', (SELECT MIN(current_week)::DATE FROM CTE_2)- interval '1 days', interval '1 days') dt
    )
    ,last_5_weeks as
    (
    SELECT  rno,current_week FROM CTE_3
    UNION
    select 5,dt::date d from generate_series( (SELECT MIN(current_week)::DATE FROM CTE_3)- interval '7 days', (SELECT MIN(current_week)::DATE FROM CTE_3)- interval '1 days', interval '1 days') dt
    )
    SELECT rno,current_week::DATE as selected_date FROM last_5_weeks order by selected_date DESC

Current output

 rno  Date
    1   "2020-10-24"
    1   "2020-10-23"
    1   "2020-10-22"
    1   "2020-10-21"
    1   "2020-10-20"
    1   "2020-10-19"
    1   "2020-10-18"
    2   "2020-10-17"
    2   "2020-10-16"
    2   "2020-10-15"
    2   "2020-10-14"
    2   "2020-10-13"
    2   "2020-10-12"
    2   "2020-10-11"
    3   "2020-10-10"
    3   "2020-10-09"
    3   "2020-10-08"
    3   "2020-10-07"
    3   "2020-10-06"
    3   "2020-10-05"
    3   "2020-10-04"
    4   "2020-10-03"
    4   "2020-10-02"
    4   "2020-10-01"
    4   "2020-09-30"
    4   "2020-09-29"
    4   "2020-09-28"
    4   "2020-09-27"
    5   "2020-09-26"
    5   "2020-09-25"
    5   "2020-09-24"
    5   "2020-09-23"
    5   "2020-09-22"
    5   "2020-09-21"
    5   "2020-09-20"

score 1 · Accepted Answer · answered Oct 22 '20 at 12:21

How about using arithmetics?

select 1 + (row_number() over(order by dt desc) - 1) / 7 rn, dt::date dt
from generate_series(
    date_trunc('week', current_date)::date + 5 - interval '5 week -1 day',
    date_trunc('week', current_date)::date + 5,
    '1 day'
) s(dt)
order by dt desc

generate_series() produces all dates at once. You control the number of weeks that are generated with the value given to week in the literal interval. Then, in the outer query, we use row_number() to enumerate the week numbers.

Demo on DB Fiddle

Stefanov.sm · Answer 2 · 2020-10-22T13:23:57.533

0

One sequence for weeks and another one for days. It is flexible, 5 is a parameter. date_trunc('week',now()+'P1W'::interval)::date-2 is this week's saturday.

select 
 w rno,
 date (date_trunc('week',now()+'P1W'::interval)::date-2 + make_interval(weeks => 1-w, days => 1-d)) "Date"
 from generate_series(1, 5, 1) w
 cross join generate_series(1, 7, 1) d;

edited Oct 22 '20 at 13:23

answered Oct 22 '20 at 12:59

Stefanov.sm

11,215
2
21
21

Numbering dates from Sunday to Saturday

2 Answers2