Discussion :

[gilgil]

bonjour,

je ne trouve pas de fonction qui permette d'extraire des enregistrements comportant toutes les données comprises entre le 15 juin et le 10 septembre par exemple mais sans tenir compte de l'année.
Par exemple, j'ai des observations de la flore avec une date d'observation (+ espèces, observateur, lieu, etc...)
Mes thématiciens flore veulent savoir ce qui a été observé entre ces deux dates (ou entre le 3 juin et le 25 juin) mais ils se fichent de l'année car ce qui les intéressent c'est la phénologies des plantes (floraison par exemple).

[estofilo]

Il y a une fonction qui donne le numéro du jour dans l'année à partir d'une date ou d'un timestamp:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

select extract(doy from la_date)

Comparer des dates indépendamment de l'année doit revenir à comparer les résultats de cette fonction appliquée aux dates en question.

[gilgil]

Merci estofilo. C'est une piste mais cela ne fonctionne pas si la période est à cheval entre 2 années (exemple de décembre à mars)
ceci fonctionne

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
select id, dateobs, extract(doy FROM dateobs) FROM t_zprospection
WHERE extract(doy FROM dateobs) between extract(DOY FROM date '2009-01-12') and extract(doy FROM date '2009-04-15')
order by extract(month FROM dateobs)

mais ceci ne renvoie rien

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
select id, dateobs, extract(doy FROM dateobs) FROM t_zprospection
WHERE extract(doy FROM dateobs) between extract(DOY FROM date '2009-12-12') and extract(doy FROM date '2009-04-15')
order by extract(month FROM dateobs)

j'ai donc écrit une fonction myself :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
CREATE OR REPLACE FUNCTION periode(dateobs date, datedebut date, datefin date)
  RETURNS boolean AS
$BODY$
declare
 
nbmois int; jd int; jf int; md int; mf int; jo int; mo int;
 
 
BEGIN
mo = extract(month FROM dateobs);--mois de la date passée
md = extract(month FROM datedebut);--mois fin
mf = extract(month FROM datefin);--mois fin
jo = extract(day FROM dateobs);--jour de la date passée
jd = extract(day FROM datedebut);--jour début
jf = extract(day FROM datefin); --jour fin
nbmois = mf - md; --nb de mois différents concernés par la période
 
--si on est dans le même mois
if nbmois = 0 then
	if mo = md and jo >=jd and jo<=jf then return true;
	end if;
-- si la période est au sein de la même année
elsif nbmois >0 then
  --si la période couvre 2 mois consécutifs
  if nbmois = 1 then
    if mo = md and jo >=jd then 
      return true; 
    end if;
    if mo = mf and jo <=jf then 
      return true;
    end if;
  --si la période couvre plus de 2 mois consécutifs
  elsif nbmois > 1 then
    if mo = md and jo >=jd then 
      return true;
    end if;
    if mo = mf and jo <=jf then 
      return true;
    end if;
    if mo between md+1 and mf-1 then 
      return true;
    end if;
  end if;
--si la période couvre 2 années 
elsif nbmois < 0 then
  --si la période couvre 2 mois consécutifs donc décembre-janvier
  if nbmois = -11 then
	if mo = md and jo >=jd then return true; 
	end if;
	if mo = mf and jo <=jf then return true;
	end if;
  --si la période couvre plus de 2 mois consécutifs
  elsif nbmois >-11 then
    if mo = md and jo >=jd then 
      return true;
    end if;
    if mo = mf and jo <=jf then 
      return true;
    end if;
    --si le mois début est décembre on commence en janvier
    if md = 12 then
      if mo between 1 and mf-1 then 
        return true;
      end if; 
    --sinon on commence le mois suivant le mois début
    else
      if mo between md+1 and mf-1 then 
        return true;
      end if;
    end if;
  end if;
end if;
	return false;	
END;
 
$BODY$
  LANGUAGE 'plpgsql';

et on l'utilise comme ça :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
select id, dateobs FROM t_zprospection
WHERE periode(dateobs, '2009-12-15', '2009-3-15') = true
order by extract(month FROM dateobs)

EDIT:
Il y a peut être une manière plus fonctionnelle et plus rapide d'écrire cette fonction avec la fonction extract(doy madate) que tu proposes.
par exemple :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
CREATE OR REPLACE FUNCTION periode(dateobs date, datedebut date, datefin date)
  RETURNS BOOLEAN AS
$BODY$
declare
 
jo int; jd int; jf int; test int; 
 
 
BEGIN
jo = extract(doy FROM dateobs);--jour de la date passée
jd = extract(doy FROM datedebut);--jour début
jf = extract(doy FROM datefin); --jour fin
test = jf - jd; --test si la période est sur 2 année ou pas
 
--si on est sur 2 années
IF test < 0 then
	IF jo BETWEEN jd AND 366 OR jo BETWEEN 1 AND jf THEN RETURN true;
	END IF;
-- si on est dans la même année
else 
	IF jo BETWEEN jd AND jf THEN RETURN true;
	END IF;
END IF;
	RETURN false;	
END;
 
$BODY$
  LANGUAGE 'plpgsql';

fonctionne un peu plus vite pour le même résultat
Merci estofilo