1

I'm looking for a solution where I can generate weekly range on two dates where the weekly range will be based on the day of the week.
Example:

date1 = 2011-12-17 date2 = 2012-05-16

Result:

week1 2011-12-17 to 2011-12-17 -- since it the first date false on a saturday
week2 2011-12-18 to 2011-12-24
...
last week 2012-05-13 to 2012-05-16

I have written my SQL in PostgreSQL:

SELECT
    time_series.cur
    ,case (6 - date_part('dow', cur)::int) = 0
    when true then cur
       else case time_series.cur + (6 - date_part('dow',cur)::int) < current_date
          when true then time_series.cur + (6 - date_part('dow', cur)::int)
          else current_date
          end
       end
    as nxt
FROM
    (select generate_series(current_date - 151
                          , current_date, '1 day')::date AS cur) time_series
where cur < current_date
and   case (current_date - 151 != cur and cur != current_date)
         when true then date_part('dow', cur)::int = 0
         else true
       end

Is there a cleaner way to do this?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Mike Montesines
  • 151
  • 4
  • 8

1 Answers1

2

This seems a bit cleaner (and much faster):

WITH x AS (
    SELECT '2011-12-17'::date AS my_start
         , '2012-05-16'::date As my_end
    )
SELECT GREATEST(d1, my_start) AS d1
      ,LEAST(d1 + 6, my_end)  AS d2
FROM  (
    SELECT generate_series(date_trunc('week', my_start + 1)
                         , date_trunc('week', my_end + 1)
                         , '1 week')::date - 1 AS d1
    FROM x
    ) d, x

Like I explained in my answer to your previous question, only this time the weeks are from Sunday to Saturday.

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228