postgresql 功能获取正确的年份周数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14856412/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Function Getting the right week number of year
提问by Houari
I want to create a function to get the right week number of year. I already posted hereto find a 'native' solution, but apparently there is not.
我想创建一个函数来获得正确的年份周数。我已经在这里发帖寻找“本机”解决方案，但显然没有。
I tryed to create funcrtion based on this mysql example
我尝试基于此mysql 示例创建功能
Here is the code translated to postgresql:
这是转换为 postgresql 的代码：
CREATE OR REPLACE FUNCTION week_num_year(_date date)
RETURNS integer AS
$BODY$declare
_year integer;
begin
select date_part('year',_date) into _year;
return ceil((to_char(_date,'DDD')::integer+(to_char(('01-01-'||_year)::date,'D')::integer%7-7))/7);
end;$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
But it gives wrong result, can someone help me ?
但它给出了错误的结果，有人可以帮助我吗？
My config: PostgreSQL 9.2
我的配置：PostgreSQL 9.2
采纳答案by Clodoaldo Neto
create or replace function week_num_year(_date date)
returns integer as
$body$
declare
_year date;
_week_number integer;
begin
select date_trunc('year', _date)::date into _year
;
with first_friday as (
select extract(doy from a::date) ff
from generate_series(_year, _year + 6, '1 day') s(a)
where extract(dow from a) = 5
)
select floor(
(extract(doy from _date) - (select ff from first_friday) - 1) / 7
) + 2 into _week_number
;
return _week_number
;
end;
$body$
language plpgsql immutable
回答by Craig Ringer
If you want proper week numbers use:
如果您想要正确的周数，请使用：
select extract(week from '2012-01-01'::date);
This will produce the result 52, which is correct if you look on a calendar.
这将产生结果52，如果您查看日历，这是正确的。
Now, if you actually want to define week numbers as "Every 7 days starting with the first day of the year" that's fine, though it doesn't match the week numbers anyone else uses and has some odd quirks:
现在，如果您真的想将周数定义为“从一年的第一天开始每 7 天”，那很好，尽管它与其他人使用的周数不匹配并且有一些奇怪的怪癖：
select floor((extract(doy from '2011-01-01'::date)-1)/7)+1;
By the way, parsing date strings and hacking them up with string functions is almost always a really bad idea.
顺便说一句，解析日期字符串并用字符串函数修改它们几乎总是一个非常糟糕的主意。
回答by Veeranjaneyulu .Kota
You can retrieve the day of the week and also the week of the year by running:
您可以通过运行来检索一周中的哪一天以及一年中的一周：
select id,extract(DOW from test_date),extract(week from test_date), testdate,name from yourtable
回答by OkieOth
What about the inbuild extract function?
inbuild提取功能怎么样？
SELECT extract (week from current_timestamp) FROM A_TABLE_FROM_YOUR_DB;
