"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);
