no way to compare when less than two revisions
Différences
Ci-dessous, les différences entre deux révisions de la page.
— | support:trucs_et_astuces:nombre_de_semaines_entre_deux_dates_quelconques [2008/10/15 16:49] (Version actuelle) – créée ioguix | ||
---|---|---|---|
Ligne 1: | Ligne 1: | ||
+ | ====== Nombre de semaines entre deux dates quelconques ====== | ||
+ | Cette fois-ci il s'agit donc de trouver le nombre de semaines entre deux dates quelconques. Ce qui rend la solution un poil compliquée, | ||
+ | |||
+ | On va donc faire ça en deux temps, d' | ||
+ | |||
+ | Déjà, trouver les années comprises entre deux dates, ainsi que le premier janvier de l' | ||
+ | |||
+ | <code sql> | ||
+ | dim=> select year, to_date(year+1, | ||
+ | from generate_series(extract(year from (current_date - interval '4 years' | ||
+ | extract(year from current_date):: | ||
+ | year | d | ||
+ | ------+------------ | ||
+ | 2003 | 01-01-2004 | ||
+ | 2004 | 01-01-2005 | ||
+ | 2005 | 01-01-2006 | ||
+ | 2006 | 01-01-2007 | ||
+ | 2007 | 01-01-2008 | ||
+ | (5 lignes) | ||
+ | </ | ||
+ | |||
+ | |||
+ | Maintenant, première ruse : si le premier janvier de l' | ||
+ | |||
+ | <code sql> | ||
+ | dim=> select extract(year from d) - 1 as year, | ||
+ | case when extract(week from d) = 1 | ||
+ | then extract(week from d - interval '1 week') | ||
+ | else extract(week from d) | ||
+ | end as nb_weeks | ||
+ | from ( | ||
+ | | ||
+ | from generate_series(extract(year from (current_date - interval '4 years' | ||
+ | extract(year from current_date):: | ||
+ | year | nb_weeks | ||
+ | ------+---------- | ||
+ | 2003 | 52 | ||
+ | 2004 | 53 | ||
+ | 2005 | 52 | ||
+ | 2006 | 52 | ||
+ | 2007 | 52 | ||
+ | (5 lignes) | ||
+ | </ | ||
+ | |||
+ | |||
+ | Il reste à additionner toutes les semaines des années intermédiaires et à ajouter le numéro de semaine de l' | ||
+ | |||
+ | <code sql> | ||
+ | create or replace function weeks(timestamp, | ||
+ | as $$ | ||
+ | select extract(week from $2)::int | ||
+ | + | ||
+ | coalesce( | ||
+ | sum( case when extract(week from d) = 1 | ||
+ | then extract(week from d - interval '1 week') | ||
+ | else extract(week from d) end ), | ||
+ | | ||
+ | - | ||
+ | | ||
+ | from ( | ||
+ | | ||
+ | from generate_series(extract(year from $1)::int, | ||
+ | extract(year from $2)::int - 1) as year | ||
+ | ) as x; | ||
+ | $$; | ||
+ | |||
+ | dim=> select weeks(current_date - interval '18 months', | ||
+ | weeks | ||
+ | ------- | ||
+ | 79 | ||
+ | (1 ligne) | ||
+ | |||
+ | dim=> select weeks(current_date - interval '4 years', | ||
+ | weeks | ||
+ | ------- | ||
+ | 209 | ||
+ | (1 ligne) </ | ||
+ | |||
+ | Et voilà :) | ||
+ | |||
+ | Note : on utilise '' | ||
+ | |||
+ | -- \\ | ||
+ | Par dim le 28/11/2007 |