Thread: How to get number of the week from datetime?

How to get number of the week from datetime?

From
"Hojdar Karel Ing."
Date:
Hi,

how I can get some agregates (avg, min, max) for whole week (in specified
year)?
For example from table with two columns : time datetime and value float8.
And I want to get average of value based on whole weeks. If I try to use
date_part('week',time) - Postgresql doesn't recognize word 'week'.

    Thanks   Karel


Re: [GENERAL] How to get number of the week from datetime?

From
Jose Soares
Date:

"Hojdar Karel Ing." wrote:

> Hi,
>
> how I can get some agregates (avg, min, max) for whole week (in specified
> year)?
> For example from table with two columns : time datetime and value float8.
> And I want to get average of value based on whole weeks. If I try to use
> date_part('week',time) - Postgresql doesn't recognize word 'week'.
>
>         Thanks   Karel
>
> ************

select date_part('dow',current_date);

If you are looking for a function to calculate the no. week of the year...
try the attached function.

José

-- ISO-8601 assigns a number to each week of the year.
-- A week that lies partly in one year and partly in another is
-- assigned a number in the year in which most of its days lie.
-- This means that:
--      Week 1 of any year is the week that contains 4 January.
-- or equivalently
--      Week 1 of any year is the week that contains the first Thrusday
--      in January .
-- If the week is 0 that means that first week is less than 4 days.

-- returns the week number of the year (1 - 53)...

drop function week(date);
create function week(date) returns int2 as
'declare
    p    int2;
    i    int2;
    week    int4;
    year    text;
    start    date;
    diff    timespan;
    mais    int2;
    txt    text;
begin
    year:= date_part(''year'',$1);
        if textlen(year) = 1 then
        year:= ''000'' || year;
    end if;
        if textlen(year) = 2 then
        year:= ''00'' || year;
    end if;
    start:= year || ''-01-01'';
    week:= date_part(''dow'',start);
    if week > 3 or week = 0 then
        mais:= 0;
    else
        mais:= 1;
    end if;
    week:= date_part(''dow'',start);
    diff:= date_part(''epoch'',$1) - date_part(''epoch'',start);
    txt:= diff;
     p:= textpos(txt,'' '');
    if p = 0 then
        i:= 0;
    else
        i:= substr(txt,1,p - 1);
        end if;
    return (i + week) / 7 + mais;
 end;
' language 'plpgsql';

select week('1997-01-01'), week('1997-12-31');
select week('1998-01-01'), week('1998-12-31');
select week('1999-01-01'), week('1999-12-31');
select week(current_date);