Function Getting the right week number of year

Question

I want to create a function to get the right week number of year. I already posted here to find a 'native' solution, but apparently there is not.

I tryed to create funcrtion based on this mysql example

Here is the code translated to 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

Adalbert

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

Ferriage

Answer 2

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.

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:

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.

Photographic

Answer 3

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

Destitution

Answer 4

What about the inbuild extract function?

SELECT extract (week from current_timestamp) FROM A_TABLE_FROM_YOUR_DB;

Tamis

No, if you test this with for example: SELECT extract (week from '2005-01-01'::date) gives 53, and should give 1, beacause this is the first week (considering that saturday is the first day of week) – Adalbert

@Adalbert Er, no, it isn't. It's the last part of the last week of the prior year. Week numbers are stupid like that and it's one of the reasons they aren't widely used. Look on a calendar to understand why. IIRC sometimes week 1 of the next year can begin during the last few days of the prior year too. – Photographic

@CraigRinger Why are you telling this 'Stupid' ?? These are requirements, and i wanted to get solution for it !! – Adalbert

@Adalbert You may have misunderstood what I said. I'm saying that "week numbers are stupid like that". This is not calling you, or your requirements, stupid. It's idiom; it could be rephrased as "week numbers are quite strange in that it's quite possible for the first few days of a year to be in the end of the last week of the previous year". See my answer for a detailed explanation. You appear to want a different week numbering scheme than the "standard" one, where the "week" begins on whatever day is the first day that year. – Photographic

Calendars have been standardised worldwide by the ISO in the standard 8601. If someone's requirements differ from that, it might be necessary to write one's own software for that. It's clearly better and helpful for intercultural business to adhere to ISO standards. – Strict