在PostgreSQL(我的版本是9.6.6)中，从周日开始，获取周数的最简单方法是什么？
DATE_PART('week',x)返回：
ISO 8601周编号的编号一年中的周。根据定义，ISO周从周一开始，一年中的第一周包含当年的1月4日。换句话说，一年中的第一个星期四在该年的第一周。(doc)
假设我的查询如下：
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 weekofyear
FROM dates
退货：
date dayname weekofyear
--------------------------------
2014-01-01 Wednesday 1
2014-01-02 Thursday 1
2014-01-03 Friday 1
2014-01-04 Saturday 1
2014-01-05 Sunday 1 <- I want this to be 2
2014-01-06 Monday 2
2014-01-07 Tuesday 2
2014-01-08 Wednesday 2
到目前为止，我已经尝试过：
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
如果一年从星期天开始，那就不太管用了。
此外，我希望第一周包含当年的1月1日。因此，如果1月1日是周六，我希望第一周是一天(而不是ISO风格的第53周)。此行为与Excel WEEKNUM函数一致。
要获得一年中的周数，周从周日开始，我们需要知道从一年的第一天到目标日期之间有多少个周日。
我在这里修改了@Erwin Brandstetter的解决方案。此解决方案计算星期日，包括一年中的第一天，不包括目标日期。
然后，因为我希望第一周(部分)是第一周(而不是零)，所以我需要添加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)