要获得一年中的周数，周从星期日开始，我们需要知道一年的第一天和目标日期之间有多少个星期日。
我改编了@Erwin Brandstetter的解决方案here。此解决方案计算星期日，包括一年的第一天，不包括目标日期。
然后，因为我希望第一周（部分）是第一周（而不是零），我需要添加1 *，除非 * 一年的第一天是星期日（在这种情况下，它已经是第一周）。

WITH dates as (SELECT generate_series(timestamp '2014-01-01', 
                                      timestamp '2014-01-31', 
                                      interval  '1 day'
                                      )::date AS date
) 
SELECT 
    date, 
    TO_CHAR(date,'Day') AS dayname,
    DATE_PART('week',date) AS week_iso,
    ((date - DATE_TRUNC('year',date)::date) + DATE_PART('isodow', DATE_TRUNC('year',date)) )::int / 7 
       + CASE WHEN DATE_PART('isodow', DATE_TRUNC('year',date)) = 7 THEN 0 ELSE 1 END  
         AS week_sundays

FROM dates

返回

date        dayname   weekofyear   week_sundays
--------------------------------
2014-01-01  Wednesday   1   1
2014-01-02  Thursday    1   1
2014-01-03  Friday      1   1
2014-01-04  Saturday    1   1
2014-01-05  Sunday      1   2
2014-01-06  Monday      2   2
2014-01-07  Tuesday     2   2

为了展示从周日开始的几年里这是如何运作的：

2017-01-01  Sunday      52  1
2017-01-02  Monday      1   1
2017-01-03  Tuesday     1   1
2017-01-04  Wednesday   1   1
2017-01-05  Thursday    1   1
2017-01-06  Friday      1   1
2017-01-07  Saturday    1   1
2017-01-08  Sunday      1   2

这项任务并不像它最初看起来那样令人生畏。它主要要求在1月1日或之后找到第一个太阳。这一天成为第一周的最后一天。从那里计算随后的几周仅仅是。一个附加的问题。另一个重要的一点是，对于周的定义，每年总是有53周，最后一周的最后一天是12月31日。下面的代码生成此周定义的年度日历。

create or replace function non_standard_cal(year_in integer)
  returns table (week_number integer, first_day_of_week date, last_day_of_week date)
  language sql immutable leakproof strict rows 53
  as $$
with recursive cal as
     (select 1 wk, d1 start_of_week, ds end_of_week, de stop_date
        from (select d1+substring( '0654321' 
                             , extract(dow from d1)::integer+1
                             , 1)::integer ds
                  , d1, de
               from ( select make_date (year_in, 1,1)      d1
                           , make_date (year_in+1, 1,1) -1 de
                    ) a
             ) b
      union all 
      select wk+1, end_of_week+1,  case when end_of_week+7 > stop_date
                                        then stop_date  
                                        else end_of_week+7
                                   end
             , stop_date
        from cal
       where wk < 53
     )                                         
select wk, start_of_week, end_of_week from cal; 
$$ ;

一般来说，我避免使用幻数，但有时它们也很有用;就像这个案子一样在幻数（实际上是一个字符串）'0654321'中，每个数字代表在1月1日或之后到达第一个星期一所需的天数，当按标准的日编号系统（0-6作为太阳-星期六）索引时。结果是星期一是第一周的最后一天。它生成递归CTE的第一行。剩下的行只是为每周添加适当的天数，直到生成53周。
以下显示了确保每周的每一天都轮到1月1日所需的年份（是的，有些日子重复）。运行个别年份以验证其日历。

do $$
declare 
  cal record;
  yr_cal cursor (yr integer) for
        select * from non_standard_cal(2000+yr) limit 1;

begin 
  for yr in 18 .. 26
  loop 
       open yr_cal(yr);
       fetch yr_cal into cal;
       raise notice 'For Year: %, week: %, first_day: %, Last_day: %, First day is: %'
                    , 2000+yr
                    ,cal.week_number
                    ,cal.first_day_of_week
                    ,cal.last_day_of_week
                    ,to_char(cal.first_day_of_week, 'Day');
       close yr_cal;
  end loop;
 end; $$;

以下可能工作-测试与两种情况下记住：

WITH dates as (SELECT generate_series(timestamp '2014-01-01', 
                                      timestamp '2014-01-10', 
                                      interval  '1 day'
                                      )::date AS date
               union
               SELECT generate_series(timestamp '2017-01-01', 
                                      timestamp '2017-01-10', 
                                      interval  '1 day'
                                      )::date AS date
) 
, alt as (
SELECT 
    date, 
    TO_CHAR(date,'Day') AS dayname,
    DATE_PART('week',date) AS week_iso,
    DATE_PART('week',date + interval '1 day') AS week_alt
FROM dates
    )
select date, dayname, 
week_iso, week_alt, case when week_alt <> week_iso 
                           then week_alt
                           else week_iso end as expected_week
from alt
order by date

输出量：

date        dayname   week_iso   week_alt expected_week
2014-01-01  Wednesday   1   1   1
2014-01-02  Thursday    1   1   1
2014-01-03  Friday      1   1   1
2014-01-04  Saturday    1   1   1
2014-01-05  Sunday      1   2   2
2014-01-06  Monday      2   2   2
2014-01-07  Tuesday     2   2   2
....
2017-01-01  Sunday      52  1   1
2017-01-02  Monday      1   1   1
2017-01-03  Tuesday     1   1   1
2017-01-04  Wednesday   1   1   1
2017-01-05  Thursday    1   1   1
2017-01-06  Friday      1   1   1
2017-01-07  Saturday    1   1   1
2017-01-08  Sunday      1   2   2

这个查询可以完美地将星期一替换为星期日作为一周的开始。
查询

SELECT CASE WHEN EXTRACT(day from '2014-01-05'::date)=4 AND
 EXTRACT(month from '2014-01-05'::date)=1 THEN date_part('week',
 '2014-01-05'::date) ELSE date_part('week', '2014-01-05'::date + 1)
 END;

输出

date_part
 -----------
          2 
(1 row)

我这样处理这个问题，以模仿excel weeknum（在插入过程中设置周数）：

CREATE OR REPLACE FUNCTION public.on_temp_trend_thi_insert()
    RETURNS trigger
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE NOT LEAKPROOF
AS $BODY$

DECLARE
    curr_week integer;
    day_of_week integer;
    v_month integer;
    v_day integer;
    v_hour integer;

BEGIN
    NEW.temp_year = extract(year from new.temp_timestamp);
    select extract(month from new.temp_timestamp) into v_month;
    NEW.temp_month = v_month;

    select extract(day from new.temp_timestamp) into v_day;
    select extract(hour from new.temp_timestamp) into v_hour;
    select extract(dow from new.temp_timestamp) into day_of_week;

    select temp_week into curr_week from temp_trend_thi where id = (new.id) - 1;
    
    if (v_month = 1 and v_day = 1 and v_hour = 0) THEN
        curr_week = 1;
    ELSIF (v_hour = 0 and day_of_week = 0) then
        curr_week = curr_week + 1;
    ELSE
        curr_week = curr_week;
    end if;

    new.temp_week = curr_week;
    
    RETURN NEW;
END;
$BODY$;