Return number of weeks in Postgres

Question

This is what I am using right now and it returns the number of days between beginDate and endDate

date( ' $(@endDate)') - date('$(@beginDate)') as weekNumber

How can I get it to return the number of weeks between the two?

As Erwin points out, it depends on what exactly you want. Do you want whole weeks? Do weeks start with the first day of 'beginDate' or do they start on a particular fixed date, so you can have a partial week on each side? Do you report partial weeks fractionally? etc. — Craig Ringer, Jan 10 '14 at 03:27

score 0 · Answer 1 · answered Jan 10 '14 at 00:51

The simplest way would be to divide the number of days by seven, although that would not take into account the starting day of the week.

You could use this instead:

extract (week from date '$(@endDate)') -extract (week from date '$(@startDate)')

which would use iso8601 week numbers. But beware of spanning years!

score 0 · Answer 2 · answered Jan 10 '14 at 09:25

Version 1:

/*
Design: 
   Week begins on day of start date
   Options:
      1) Count only whole weeks
      2) Count partial weeks on the right side (end date side)
*/

   select 
      sum(case when ('2013-02-08'::date - ind::date) >= 7 then 1 else 0 end) as whole_weeks,
      count(*) as partial_right
   from 
      generate_series('2013-01-01'::date /*dow=2*/,'2013-02-05'::date /*dow=2*/,'7 days') ind

Version 2:

/*
Design: 
   Week begins on specific day of week (5 chosen in this example)   
   Options:
      1) Count only whole weeks
      2) Count partial weeks on the right side (end date side)
      3) Count partial weeks on the left side (start date side)
      4) Count partial weeks on both sides
*/

select
   sum(case when days = 7 then 1 else 0 end) as whole_weeks,
   sum(case when days = 7 or max_ind = week_start then 1 else 0 end) as partial_right,
   sum(case when days = 7 or week_start < min_ind then 1 else 0 end) as partial_left,
   count(*) as partial_both_sides
from
 (
   select
      ind - (case when dow < bow then dow + 7 - bow else dow - bow end)::int as week_start,
      count(*) as days,
      min(ind) as min_ind,
      max(ind) as max_ind
    from 
      (select 
         ind::date as ind, 
         extract(isodow from ind) as dow,
         5::int as bow 
      from 
         generate_series('2013-01-01'::date /*dow=2*/,'2013-02-08'::date /*dow=5*/,'1 day') ind
      ) inp
   group by
      week_start
 ) t

Return number of weeks in Postgres

2 Answers2