Function Getting the correct week day of the year

Question

I want to create a function to get the right week of the year. I already posted here to find a "native" solution, but apparently this does not happen.

I tried to create funcrtion based on this mysql example

Here is the code translated into 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 the wrong result, can someone help me?

My config: PostgreSQL 9.2

Answer 1

 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 

Answer 2

If you want to use the correct week numbers:

 select extract(week from '2012-01-01'::date); 

This will result in result 52 , which is correct if you look at the calendar.

Now, if you really want to define the number of weeks as “Every 7 days, starting on the first day of the year,” that’s good, although it doesn’t match the week numbers that anyone uses and has some odd quirks:

 select floor((extract(doy from '2011-01-01'::date)-1)/7)+1; 

By the way, parsing date strings and breaking them with string functions is almost always very bad.

Answer 3

What about inbuild extraction function?

 SELECT extract (week from current_timestamp) FROM A_TABLE_FROM_YOUR_DB; 

Answer 4

You can get the day of the week as well as the week of the year by doing:

  select id,extract(DOW from test_date),extract(week from test_date), testdate,name from yourtable