Weeks between two dates

Question

I'm attempting to turn two dates into a series of records. One record for each week between the dates.

Additionally the original start and end dates should be used to clip the week in case the range starts or ends mid-week. I'm also assuming that a week starts on Monday.

With a start date of: 05/09/2018 and an end date of 27/09/2018 I would like to retrieve the following results:

| # | Start Date   | End date     |
|---------------------------------|
| 0 | '05/09/2018' | '09/09/2018' |
| 1 | '10/09/2018' | '16/09/2018' |
| 2 | '17/09/2018' | '23/09/2018' |
| 3 | '24/09/2018' | '27/09/2018' |

I have made some progress - at the moment I can get the total number of weeks between the date range with:

SELECT (
  EXTRACT(
      days FROM (
                  date_trunc('week', to_date('27/09/2018', 'DD/MM/YYYY')) -
                  date_trunc('week', to_date('05/09/2018', 'DD/MM/YYYY'))
                ) / 7
  ) + 1
) as total_weeks;

Total weeks will return 4 for the above SQL. This is where I'm stuck, going from an integer to actual set of results.

score 1 · Answer 1 · answered Sep 18 '18 at 11:55

1

Use generate_series():

select gs.*
from generate_series(date_trunc('week', '2018-09-05'::date),
                     '2018-09-27'::date,
                     interval '1 week'
                    ) gs(dte)

answered Sep 18 '18 at 11:55

Gordon Linoff

1,242,037
58
646
786

This didn't answer the whole question, however it was what I used in my solution, I think generate_series is quite an elegant function. – Royal Wares Sep 18 '18 at 13:10

Laurenz Albe · Accepted Answer · 2018-09-18T13:24:07.630

1

Window functions are your friend:

SELECT week_num,
       min(d) AS start_date,
       max(d) AS end_date
FROM (SELECT d,
             count(*) FILTER (WHERE new_week) OVER (ORDER BY d) AS week_num
      FROM (SELECT DATE '2018-09-05' + i AS d,
                   extract(dow FROM DATE '2018-09-05'
                                    + lag(i) OVER (ORDER BY i)
                          ) = 1 AS new_week
            FROM generate_series(0, DATE '2018-09-27' - DATE '2018-09-05') AS i
           ) AS week_days
     ) AS weeks
GROUP BY week_num
ORDER BY week_num;

 week_num | start_date |  end_date  
----------+------------+------------
        0 | 2018-09-05 | 2018-09-09
        1 | 2018-09-10 | 2018-09-16
        2 | 2018-09-17 | 2018-09-23
        3 | 2018-09-24 | 2018-09-27
(4 rows)

edited Sep 18 '18 at 13:24

answered Sep 18 '18 at 13:01

Laurenz Albe

209,280
17
206
263

This works for the start & end dates but the week num sometimes starts at 1 when different start dates are used. – Royal Wares Sep 18 '18 at 13:08
1

One more window function, and the problem should be fixed. – Laurenz Albe Sep 18 '18 at 13:24

score 0 · Answer 3 · answered Sep 18 '18 at 13:12

Ultimately I expanded on Gordon's solution to get to the following, however Laurenz's answer is slightly more concise.

select
  (
    case when (week_start - interval '6 days' <= date_trunc('week', '2018-09-05'::date)) then '2018-09-05'::date else week_start end
  ) as start_date,
  (
    case when (week_start + interval '6 days' >= '2018-09-27'::date) then '2018-09-27'::date else week_start + interval '6 days' end
  ) as end_date
from generate_series(
         date_trunc('week', '2018-09-05'::date),
         '2018-09-27'::date,
          interval '1 week'
      ) gs(week_start);

Weeks between two dates

3 Answers3

Linked