CODEWITHSUNDEEP
sqlpostgresql
Royal Wares
Royal Wares

Reputation: 1272

Weeks between two dates

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.

Upvotes: 1

Views: 1267

Answers (3)

Laurenz Albe
Laurenz Albe

Reputation: 247950

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)

Upvotes: 1

Royal Wares
Royal Wares

Reputation: 1272

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);

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271003

Use generate_series():

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

Upvotes: 1

Related Questions