Re: Numero de semana + fechas

2010/1/14 Raúl Andrés Duque Murillo <ra_duque(at)yahoo(dot)com(dot)mx>:
> Que tal Jorge.
>
> Utilizando un generate_series puedes lograr tu objetivo de forma sencilla.
>

Pero aun prefiero la forma con el WITH RECURSIVE, es mas elegante ;)

select to_char(extract(week from (f1 + (i || ' weeks')::interval)),
'09') as num,
(f1 + (i || ' weeks')::interval)::date,
(f2 + (i || ' weeks')::interval)::date
from (select f1, f1 + 6 as f2
from (select (current_date -
(extract(week from current_date) - 1 || '
weeks')::interval)::date -
(extract(dow from current_date)::integer - 1) as f1
) as foo1
) as foo2, generate_series(0, 52) as i
where extract(year from (f1 + (i || ' weeks')::interval)) = 2010

en forma de función:

CREATE OR REPLACE FUNCTION fechas_anio(anio integer) RETURNS SETOF record AS
$$
select to_char(extract(week from (f1 + (i || ' weeks')::interval)),
'09') as num,
(f1 + (i || ' weeks')::interval)::date,
(f2 + (i || ' weeks')::interval)::date
from (select f1, f1 + 6 as f2
from (select (($1 || '-02-01')::date -
(extract(week from ($1 || '-02-01')::date) -
1 || ' weeks')::interval)::date -
(extract(dow from ($1 ||
'-02-01')::date)::integer - 1) as f1
) as foo1
) as foo2, generate_series(0, 52) as i
where extract(year from (f1 + (i || ' weeks')::interval)) = $1;
$$ language sql

y lo llamarías asi (fijate que debes incluir el formato de retorno o
crear un tipo para esto):

select * from fechas_anio(2010) as (semana text, fecha_inicial date,
fecha_final date)

--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157