digoal
2021-01-29
PostgreSQL , week , 1月1号是不是新年第一周
一周有7天, 一年有52或53周, 那么第一周是怎么算的? 我们可能理所当然觉得1月1号是第一周, 实际上是错的, 在数据库中, ISO 8601标准里面, 1月1号可能是第一周, 也可能是去年的最后一周, 甚至1月2号, 1月3号都有可能是去年的最后一周.
The number of the ISO 8601 week-numbering week of the year. By definition, ISO weeks start on Mondays and the first week of a year contains January 4 of that year. In other words, the first Thursday of a year is in week 1 of that year.
In the ISO week-numbering system, it is possible for early-January dates to be part of the 52nd or 53rd week of the previous year, and for late-December dates to be part of the first week of the next year.
For example,
- 2005-01-01 is part of the 53rd week of year 2004,
- and 2006-01-01 is part of the 52nd week of year 2005,
- while 2012-12-31 is part of the first week of 2013.
It's recommended to use the isoyear field together with week to get consistent results.
SELECT EXTRACT(WEEK FROM TIMESTAMP '2001-02-16 20:38:40');
Result: 7
所以最坏的情况1月1,2,3刚好是周五,六,日时, 那么这年的第一周是从1月4号开始的.
只有当1月1号是周一、二、三、四时, 1月1号才是这一年的第一周.
postgres=# SELECT EXTRACT(week FROM x) , EXTRACT(isodow FROM x) from (values (DATE '2006-01-01')) t (x);
date_part | date_part
-----------+-----------
52 | 7
(1 row)
postgres=# SELECT EXTRACT(week FROM x) , EXTRACT(isodow FROM x) from (values (DATE '2005-01-01')) t (x);
date_part | date_part
-----------+-----------
53 | 6
(1 row)
postgres=# SELECT EXTRACT(week FROM x) , EXTRACT(isodow FROM x) from (values (DATE '2004-01-01')) t (x);
date_part | date_part
-----------+-----------
1 | 4
(1 row)
create or replace function myweek(date) returns int as $$
select case
when EXTRACT(isodow FROM date_trunc('year' , $1)) >= 5
and to_char($1,'mm') <> '01'
then EXTRACT(week FROM $1) + 1
when EXTRACT(isodow FROM date_trunc('year' , $1)) >= 5
and to_char($1,'mm') = '01'
and EXTRACT(week FROM $1) > 40
then 1
when EXTRACT(isodow FROM date_trunc('year' , $1)) >= 5
and to_char($1,'mm') = '01'
and EXTRACT(week FROM $1) < 40
then EXTRACT(week FROM $1) + 1
else
EXTRACT(week FROM $1)
end ;
$$ language sql strict;
postgres=# SELECT EXTRACT(week FROM x) , EXTRACT(isodow FROM x) , myweek(x) from (values (DATE '2005-01-01')) t (x);
date_part | date_part | myweek
-----------+-----------+--------
53 | 6 | 1
(1 row)
postgres=# SELECT EXTRACT(week FROM x) , EXTRACT(isodow FROM x) , myweek(x) from (values (DATE '2005-01-02')) t (x);
date_part | date_part | myweek
-----------+-----------+--------
53 | 7 | 1
(1 row)
postgres=# SELECT EXTRACT(week FROM x) , EXTRACT(isodow FROM x) , myweek(x) from (values (DATE '2005-01-03')) t (x);
date_part | date_part | myweek
-----------+-----------+--------
1 | 1 | 2
(1 row)
postgres=# SELECT EXTRACT(week FROM x) , EXTRACT(isodow FROM x) , myweek(x) from (values (DATE '2005-12-31')) t (x);
date_part | date_part | myweek
-----------+-----------+--------
52 | 6 | 53
(1 row)
create or replace function myweek1(date) returns int as $$
select ceil(EXTRACT(doy FROM $1)/7.0)::int;
$$ language sql strict;
postgres=# SELECT EXTRACT(week FROM x) , EXTRACT(isodow FROM x) , myweek(x) , EXTRACT(doy FROM x) , myweek1(x) from (values (DATE '2005-01-07')) t (x);
date_part | date_part | myweek | date_part | myweek1
-----------+-----------+--------+-----------+---------
1 | 5 | 2 | 7 | 1
(1 row)
postgres=# SELECT EXTRACT(week FROM x) , EXTRACT(isodow FROM x) , myweek(x) , EXTRACT(doy FROM x) , myweek1(x) from (values (DATE '2005-01-01')) t (x);
date_part | date_part | myweek | date_part | myweek1
-----------+-----------+--------+-----------+---------
53 | 6 | 1 | 1 | 1
(1 row)
postgres=# SELECT EXTRACT(week FROM x) , EXTRACT(isodow FROM x) , myweek(x) , EXTRACT(doy FROM x) , myweek1(x) from (values (DATE '2005-01-08')) t (x);
date_part | date_part | myweek | date_part | myweek1
-----------+-----------+--------+-----------+---------
1 | 6 | 2 | 8 | 2
(1 row)
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
