Intervalle entre deux dates en secondes

Version imprimable

07/11/2012, 12h23
lllhuhdklll

Intervalle entre deux dates en secondes

Bonjour, je vous explique :

J'ai deux dates : par exemple :
13/06/2012 18:15:00 ET 16/06/2012 09:30

Je voudrais faire une différence entre ces deux dates.
Mais je veux que la plage d'horaire entre 19h et 07h ne sois pas prise en compte.
Je veux avoir le résultat de la différence en secondes.
Pour l'exemple (en calcul à la main), on arrive à 15h30 de différence, sois 55800 secondes.

J'ai entendu parler de la fonction Modulo mais je ne comprend pas comment ceci fonctionne.

Merci pour votre aide.
07/11/2012, 14h54
ojo77

Code:

select (date2-date1)*24*60*60 from dual;
07/11/2012, 14h59
lllhuhdklll

Citation:

Envoyé par ojo77

Code:

select (date2-date1)*24*60*60 from dual;

C'est un bon début, mais la plage entre 19h et 7h (plage de nuit) ne dois pas être prise en compte...

Citation:

Envoyé par lllhuhdklll

Pour l'exemple (en calcul à la main), on arrive à 15h30 de différence, sois 55800 secondes.

C'est faux, il manque une journée...
En SQL :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
  with t as (
select to_date('13/06/2012 18:15','dd/mm/yyyy hh24:mi') as d_deb,
       to_date('16/06/2012 09:30','dd/mm/yyyy hh24:mi') as d_fin
  from dual),
       t_07_19 as (
select case when d_deb >= to_date(to_char(d_deb,'YYYYMMDD')||'19:00','YYYYMMDDhh24:mi')
            then to_date(to_char(d_deb + 1,'YYYYMMDD')||'07:00','YYYYMMDDhh24:mi')
            when d_deb <= to_date(to_char(d_deb,'YYYYMMDD')||'07:00','YYYYMMDDhh24:mi')
            then to_date(to_char(d_deb,'YYYYMMDD')||'07:00','YYYYMMDDhh24:mi')
            else d_deb
        end as d_deb,
       case when d_fin >= to_date(to_char(d_fin,'YYYYMMDD')||'19:00','YYYYMMDDhh24:mi')
            then to_date(to_char(d_fin,'YYYYMMDD')||'19:00','YYYYMMDDhh24:mi')
            when d_fin <= to_date(to_char(d_fin,'YYYYMMDD')||'07:00','YYYYMMDDhh24:mi')
            then to_date(to_char(d_fin - 1,'YYYYMMDD')||'19:00','YYYYMMDDhh24:mi')
            else d_fin
        end as d_fin
   from t
)
select   (d_fin - to_date(to_char(d_fin,'YYYYMMDD')||'07:00','YYYYMMDDhh24:mi'))*24*60*60
       + (to_date(to_char(d_deb,'YYYYMMDD')||'19:00','YYYYMMDDhh24:mi') - d_deb)*24*60*60
       + (trunc(d_fin - 1) - trunc(d_deb + 1) +1)*12*60*60
  from t_07_19

Mais une fonction PL/SQL est plus utile/maintenable (ou sinon créer une vue):

Code:

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
create or replace function func_DureeSec (
          p_date_deb date, 
          p_date_fin date, 
          p_heure_debut varchar2 default '07:00', 
          p_heure_fin varchar2 default '19:00') 
return number deterministic as
    l_date_deb date;
    l_date_fin date;
begin
 
    if (p_date_deb >= to_date(to_char(p_date_deb,'YYYYMMDD')||p_heure_fin,'YYYYMMDDhh24:mi')) then
        l_date_deb := to_date(to_char(p_date_deb + 1,'YYYYMMDD')||p_heure_debut,'YYYYMMDDhh24:mi');
    elsif (p_date_deb <= to_date(to_char(p_date_deb,'YYYYMMDD')||p_heure_debut,'YYYYMMDDhh24:mi')) then
        l_date_deb := to_date(to_char(p_date_deb,'YYYYMMDD')||p_heure_debut,'YYYYMMDDhh24:mi');
    else 
        l_date_deb := p_date_deb;
    end if;
 
    if (p_date_fin >= to_date(to_char(p_date_fin,'YYYYMMDD')||p_heure_fin,'YYYYMMDDhh24:mi')) then
        l_date_fin := to_date(to_char(p_date_fin,'YYYYMMDD')||p_heure_fin,'YYYYMMDDhh24:mi');
    elsif (p_date_fin <= to_date(to_char(p_date_fin,'YYYYMMDD')||p_heure_debut,'YYYYMMDDhh24:mi')) then
        l_date_fin := to_date(to_char(p_date_fin - 1,'YYYYMMDD')||p_heure_fin,'YYYYMMDDhh24:mi');
    else 
        l_date_fin := p_date_fin;
    end if;
 
    return (l_date_fin - to_date(to_char(l_date_fin,'YYYYMMDD')||p_heure_debut,'YYYYMMDDhh24:mi'))*24*60*60
         + (to_date(to_char(l_date_deb,'YYYYMMDD')||p_heure_fin,'YYYYMMDDhh24:mi') - l_date_deb)*24*60*60
         + (trunc(l_date_fin - 1) - trunc(l_date_deb + 1) +1) * ( to_date('19000101'||p_heure_fin,'YYYYMMDDhh24:mi')
                                                                 -to_date('19000101'||p_heure_debut,'YYYYMMDDhh24:mi'))*24*60*60;
 
end func_DureeSec;
/

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
SQL>   with t as (
  2  select to_date('13/06/2012 18:15','dd/mm/yyyy hh24:mi') as d_deb, to_date('16/06/2012 9:30','dd/mm/yyyy hh24:mi') as d_fin from dual union all
  3  select to_date('13/06/2012 18:15','dd/mm/yyyy hh24:mi') as d_deb, to_date('14/06/2012 02:30','dd/mm/yyyy hh24:mi') as d_fin from dual union all
  4  select to_date('13/06/2012 18:15','dd/mm/yyyy hh24:mi') as d_deb, to_date('13/06/2012 18:30','dd/mm/yyyy hh24:mi') as d_fin from dual union all
  5  select to_date('13/06/2012 22:15','dd/mm/yyyy hh24:mi') as d_deb, to_date('16/06/2012 09:30','dd/mm/yyyy hh24:mi') as d_fin from dual union all
  6  select to_date('13/06/2012 22:15','dd/mm/yyyy hh24:mi') as d_deb, to_date('14/06/2012 05:30','dd/mm/yyyy hh24:mi') as d_fin from dual
  7  )
  8  select d_deb, d_fin,
  9         (select func_DureeSec(d_deb, d_fin) from dual) as dureesec
 10    from t;
 
D_DEB               D_FIN                 DUREESEC
------------------- ------------------- ----------
13/06/2012 18:15:00 16/06/2012 09:30:00      98100
13/06/2012 18:15:00 14/06/2012 02:30:00       2700
13/06/2012 18:15:00 13/06/2012 18:30:00        900
13/06/2012 22:15:00 16/06/2012 09:30:00      95400
13/06/2012 22:15:00 14/06/2012 05:30:00          0
 
SQL>

Citation:

Envoyé par skuatamad

C'est faux, il manque une journée...

Oups... Exact.

Citation:

Envoyé par skuatamad

Mais une fonction PL/SQL est plus utile/maintenable (ou sinon créer une vue):

Code:

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
create or replace function func_DureeSec (
          p_date_deb date, 
          p_date_fin date, 
          p_heure_debut varchar2 default '07:00', 
          p_heure_fin varchar2 default '19:00') 
return number deterministic as
    l_date_deb date;
    l_date_fin date;
begin
 
    if (p_date_deb >= to_date(to_char(p_date_deb,'YYYYMMDD')||p_heure_fin,'YYYYMMDDhh24:mi')) then
        l_date_deb := to_date(to_char(p_date_deb + 1,'YYYYMMDD')||p_heure_debut,'YYYYMMDDhh24:mi');
    elsif (p_date_deb <= to_date(to_char(p_date_deb,'YYYYMMDD')||p_heure_debut,'YYYYMMDDhh24:mi')) then
        l_date_deb := to_date(to_char(p_date_deb,'YYYYMMDD')||p_heure_debut,'YYYYMMDDhh24:mi');
    else 
        l_date_deb := p_date_deb;
    end if;
 
    if (p_date_fin >= to_date(to_char(p_date_fin,'YYYYMMDD')||p_heure_fin,'YYYYMMDDhh24:mi')) then
        l_date_fin := to_date(to_char(p_date_fin,'YYYYMMDD')||p_heure_fin,'YYYYMMDDhh24:mi');
    elsif (p_date_fin <= to_date(to_char(p_date_fin,'YYYYMMDD')||p_heure_debut,'YYYYMMDDhh24:mi')) then
        l_date_fin := to_date(to_char(p_date_fin - 1,'YYYYMMDD')||p_heure_fin,'YYYYMMDDhh24:mi');
    else 
        l_date_fin := p_date_fin;
    end if;
 
    return (l_date_fin - to_date(to_char(l_date_fin,'YYYYMMDD')||p_heure_debut,'YYYYMMDDhh24:mi'))*24*60*60
         + (to_date(to_char(l_date_deb,'YYYYMMDD')||p_heure_fin,'YYYYMMDDhh24:mi') - l_date_deb)*24*60*60
         + (trunc(l_date_fin - 1) - trunc(l_date_deb + 1) +1) * ( to_date('19000101'||p_heure_fin,'YYYYMMDDhh24:mi')
                                                                 -to_date('19000101'||p_heure_debut,'YYYYMMDDhh24:mi'))*24*60*60;
 
end func_DureeSec;
/

Euh, comment dire... Un grand MERCI !!!
C'est exactement ce qu'il me fallais !!!
C'est vrai que c'est un peu compliqué quand ça fais seulement 1 mois que je me suis mis au PL/SQL.

07/11/2012, 17h00
skuatamad

Une version qui tient compte des jours fériés et WE qui s'appuie sur le calendrier proposé par waldar :
Calendrier avec jours fériés
[edit] code faux voir message plus bas

[edit]
Bon une nouvelle version un peu plus compréhensible, j'avais fait un truc sioux sans vraiment le vouloir et qui ne fonctionnait pas pour les JO :

Code:

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
create or replace function func_DureeSec (
          p_date_deb date, 
          p_date_fin date, 
          p_heure_debut varchar2 default '07:00', 
          p_heure_fin varchar2 default '19:00') 
return number deterministic as
    l_date_deb date;
    l_date_fin date;
begin
 
    -- Si l'heure de la date de début est supérieure à l'heure de fin de travail (19:00)
    -- ==> La date de début est le lendemain à 07h
    if (p_date_deb >= to_date(to_char(p_date_deb,'YYYYMMDD')||p_heure_fin,'YYYYMMDDhh24:mi')) then
        l_date_deb := to_date(to_char(p_date_deb + 1,'YYYYMMDD')||p_heure_debut,'YYYYMMDDhh24:mi');
    -- Si l'heure de la date de début est inférieure à l'heure de début de travail (07:00)
    -- ==> La date de début est la date de début à 07h
    elsif (p_date_deb <= to_date(to_char(p_date_deb,'YYYYMMDD')||p_heure_debut,'YYYYMMDDhh24:mi')) then
        l_date_deb := to_date(to_char(p_date_deb,'YYYYMMDD')||p_heure_debut,'YYYYMMDDhh24:mi');
    else 
    -- Sinon on garde la date de début telle quelle
        l_date_deb := p_date_deb;
    end if;
 
    -- Si l'heure de la date de fin est supérieure à l'heure de fin de travail (19:00)
    -- ==> La date de fin est la date de fin à 19h
    if (p_date_fin >= to_date(to_char(p_date_fin,'YYYYMMDD')||p_heure_fin,'YYYYMMDDhh24:mi')) then
        l_date_fin := to_date(to_char(p_date_fin,'YYYYMMDD')||p_heure_fin,'YYYYMMDDhh24:mi');
    -- Si l'heure de la date de fin est inférieure à l'heure de début de travail (07:00)
    -- ==> La date de fin est la veille à 19h
    elsif (p_date_fin <= to_date(to_char(p_date_fin,'YYYYMMDD')||p_heure_debut,'YYYYMMDDhh24:mi')) then
        l_date_fin := to_date(to_char(p_date_fin - 1,'YYYYMMDD')||p_heure_fin,'YYYYMMDDhh24:mi');
    else 
    -- Sinon on garde la date de fin telle quelle
        l_date_fin := p_date_fin;
    end if;
 
    /* Le délai est calculé comme :
        1/ "délai écoulé le dernier jour" + "délai écoulé le 1er jour" + "délai écoulé pendant les jours entre".
        2/ date1 - date2 donne un résultat en nombre de jour (*24*60*60) pour avoir un résultat en seconde
        3/ to_date('19000101'||p_heure_fin,'YYYYMMDDhh24:mi')-to_date('19000101'||p_heure_debut,'YYYYMMDDhh24:mi') 
           ==> ici 0.5 car les journées ne font pas 24h mais seulement 12h
    */
 
    if l_date_fin < l_date_deb then
        return 0;
    elsif (trunc(l_date_deb) = trunc(l_date_fin)) then
        return (l_date_fin - l_date_deb)*24*60*60;    
    else 
        return (l_date_fin - to_date(to_char(l_date_fin,'YYYYMMDD')||p_heure_debut,'YYYYMMDDhh24:mi'))*24*60*60
             + (to_date(to_char(l_date_deb,'YYYYMMDD')||p_heure_fin,'YYYYMMDDhh24:mi') - l_date_deb)*24*60*60
             + (trunc(l_date_fin - 1) - trunc(l_date_deb + 1) +1) * ( to_date('19000101'||p_heure_fin,'YYYYMMDDhh24:mi')
                                                                      -to_date('19000101'||p_heure_debut,'YYYYMMDDhh24:mi'))*24*60*60;
    end if;
end func_DureeSec;

Et pour les jours ouvrés

Code:

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
create or replace function func_DureeSecJO (
          p_date_deb date, 
          p_date_fin date, 
          p_heure_debut varchar2 default '07:00', 
          p_heure_fin varchar2 default '19:00') 
return number as
    l_date_deb date;
    l_date_fin date;
    l_nb_jour_ouvre number;
begin
 
    if (p_date_deb >= to_date(to_char(p_date_deb,'YYYYMMDD')||p_heure_fin,'YYYYMMDDhh24:mi')) then
        l_date_deb := to_date(to_char(p_date_deb + 1,'YYYYMMDD')||p_heure_debut,'YYYYMMDDhh24:mi');
    elsif (p_date_deb <= to_date(to_char(p_date_deb,'YYYYMMDD')||p_heure_debut,'YYYYMMDDhh24:mi')) then
        l_date_deb := to_date(to_char(p_date_deb,'YYYYMMDD')||p_heure_debut,'YYYYMMDDhh24:mi');
    else 
        l_date_deb := p_date_deb;
    end if;
 
    if (p_date_fin >= to_date(to_char(p_date_fin,'YYYYMMDD')||p_heure_fin,'YYYYMMDDhh24:mi')) then
        l_date_fin := to_date(to_char(p_date_fin,'YYYYMMDD')||p_heure_fin,'YYYYMMDDhh24:mi');
    elsif (p_date_fin <= to_date(to_char(p_date_fin,'YYYYMMDD')||p_heure_debut,'YYYYMMDDhh24:mi')) then
        l_date_fin := to_date(to_char(p_date_fin - 1,'YYYYMMDD')||p_heure_fin,'YYYYMMDDhh24:mi');
    else 
        l_date_fin := p_date_fin;
    end if;
 
    if l_date_fin < l_date_deb then
        return 0;
    elsif (trunc(l_date_deb) = trunc(l_date_fin)) then
        return (l_date_fin - l_date_deb)*24*60*60;    
    else 
        select count(*) - 1 into l_nb_jour_ouvre
          from v_calendrier
         where cal_jour between l_date_deb and l_date_fin
           and cal_position_semaine NOT IN ('6', '7')
           and clf_chome = 0;
 
        return (l_date_fin - to_date(to_char(l_date_fin,'YYYYMMDD')||p_heure_debut,'YYYYMMDDhh24:mi'))*24*60*60
             + (to_date(to_char(l_date_deb,'YYYYMMDD')||p_heure_fin,'YYYYMMDDhh24:mi') - l_date_deb)*24*60*60
             + l_nb_jour_ouvre * ( to_date('19000101'||p_heure_fin,'YYYYMMDDhh24:mi')
                                  -to_date('19000101'||p_heure_debut,'YYYYMMDDhh24:mi'))*24*60*60;
 
    end if;
 
end func_DureeSecJO;

J'espère que là c'est mieux, mais à quand même bien tester avant utilisation.
[edit2] Une autre version de func_DureeSecJO pour mieux gérer les bornes plus bas....

En fait pour les jours ouvrés, j'ai une fonction qui calcul de nombre (même deux fonctions : une pour les week end et une pour les jours fériés (en fonction d'une table JR_FERIE dans la BDD).

Concernant les 3 étapes du calcul du délai, c'est bien cela :
Étape 1 :
Code:

1 2 (l_date_fin - to_date(to_char(l_date_fin,'YYYYMMDD')||p_heure_debut,'YYYYMMDDhh24:mi'))*24*60*60
Étape 2 :
Code:

1 2 (to_date(to_char(l_date_deb,'YYYYMMDD')||p_heure_fin,'YYYYMMDDhh24:mi') - l_date_deb)*24*60*60
Étape 3 :
Code:

1 2 3 (trunc(l_date_fin - 1) - trunc(l_date_deb + 1) +1) * ( to_date('19000101'||p_heure_fin,'YYYYMMDDhh24:mi') -to_date('19000101'||p_heure_debut,'YYYYMMDDhh24:mi'))*24*60*60;

Citation:

Envoyé par lllhuhdklll

Concernant les 3 étapes du calcul du délai, c'est bien cela :

Oui.

Une nouvelle version de la fonction qui prend en compte les jours ouvrés pour mieux gérer les bornes (du moins j'espère :aie:)
L'idée est que si la date de début est un samedi et la date de fin un dimanche par exemple, le délai sera 0 :

Code:

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
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
create or replace function func_DureeSecJO (
          p_date_deb date, 
          p_date_fin date, 
          p_heure_debut varchar2 default '07:00', 
          p_heure_fin varchar2 default '19:00') 
return number as
    l_date_deb date;
    l_date_fin date;
    l_date_deb_ouvre date;
    l_date_fin_ouvre date;
    l_nb_jour_ouvre_entre number;
    l_delai number;
begin
 
    if p_date_fin < p_date_deb then
        return 0;
    end if;
 
    -- Si l'heure de la date de début est supérieure à l'heure de fin de travail (19:00)
    -- ==> La date de début est le lendemain à 07h
    if (p_date_deb >= to_date(to_char(p_date_deb,'YYYYMMDD')||p_heure_fin,'YYYYMMDDhh24:mi')) then
        l_date_deb := to_date(to_char(p_date_deb + 1,'YYYYMMDD')||p_heure_debut,'YYYYMMDDhh24:mi');
    -- Si l'heure de la date de début est inférieure à l'heure de début de travail (07:00)
    -- ==> La date de début est la date de début à 07h
    elsif (p_date_deb <= to_date(to_char(p_date_deb,'YYYYMMDD')||p_heure_debut,'YYYYMMDDhh24:mi')) then
        l_date_deb := to_date(to_char(p_date_deb,'YYYYMMDD')||p_heure_debut,'YYYYMMDDhh24:mi');
    else 
    -- Sinon on garde la date de début telle quelle
	l_date_deb := p_date_deb;
    end if;
 
    -- Si l'heure de la date de fin est supérieure à l'heure de fin de travail (19:00)
    -- ==> La date de fin est la date de fin à 19h
    if (p_date_fin >= to_date(to_char(p_date_fin,'YYYYMMDD')||p_heure_fin,'YYYYMMDDhh24:mi')) then
        l_date_fin := to_date(to_char(p_date_fin,'YYYYMMDD')||p_heure_fin,'YYYYMMDDhh24:mi');
    -- Si l'heure de la date de fin est inférieure à l'heure de début de travail (07:00)
    -- ==> La date de fin est la veille à 19h
    elsif (p_date_fin <= to_date(to_char(p_date_fin,'YYYYMMDD')||p_heure_debut,'YYYYMMDDhh24:mi')) then
        l_date_fin := to_date(to_char(p_date_fin - 1,'YYYYMMDD')||p_heure_fin,'YYYYMMDDhh24:mi');
    else 
    -- Sinon on garde la date de fin telle quelle
        l_date_fin := p_date_fin;
    end if;          
 
    if l_date_fin < l_date_deb then        
        return 0;
    end if;
 
    -- Gestion des bornes, on prend la première date ouvrée > l_date_deb et la dernière date ouvrée < l_date_fin
    -- Si aucune ligne sélectionnée => to_date('31/12/9999','dd/mm/yyyy') pour obtenir 0
    -- Calcul du nombre de jours strictment entre les date min et date max
      with jours_ouvres as (
    select cal_jour
      from v_calendrier
     where cal_jour between trunc(l_date_deb) and trunc(l_date_fin)
       and cal_position_semaine NOT IN ('6', '7')
       and clf_chome = 0
    ),
           min_max_jours as (
    select min(to_date(to_char(cal_jour,'YYYYMMDD')||to_char(l_date_deb,'hh24:mi:ss'),'YYYYMMDDhh24:mi:ss')) as min_jour,
           max(to_date(to_char(cal_jour,'YYYYMMDD')||to_char(l_date_fin,'hh24:mi:ss'),'YYYYMMDDhh24:mi:ss')) as max_jour
      from jours_ouvres
    )
    select coalesce(max(min_jour),to_date('31/12/9999','dd/mm/yyyy')), 
           coalesce(max(max_jour),to_date('31/12/9999','dd/mm/yyyy')), 
           coalesce(sum(case when cal_jour > trunc(min_jour) and cal_jour < trunc(max_jour) then 1 else 0 end),0) as nb_jours
      into l_date_deb_ouvre, l_date_fin_ouvre, l_nb_jour_ouvre_entre
      from jours_ouvres
     cross join min_max_jours;
 
    -- Si la date ouvrée de début est supérieure à la date de début
    -- ==> La date de début est la date ouvrée à 07h
    if l_date_deb_ouvre > l_date_deb then
       l_date_deb := to_date(to_char(l_date_deb_ouvre,'YYYYMMDD')||p_heure_debut,'YYYYMMDDhh24:mi');
    end if;
 
    -- Si la date ouvrée de fin est inférieure à la date de fin
    -- ==> La date de fin est la date ouvrée à 19h
    if l_date_fin_ouvre < l_date_fin then
       l_date_fin := to_date(to_char(l_date_fin_ouvre,'YYYYMMDD')||p_heure_fin,'YYYYMMDDhh24:mi');
    end if;
 
    --dbms_output.put_line(l_date_deb ||' --> '|| l_date_fin ||'-->'|| l_nb_jour_ouvre_entre);
    if l_date_fin < l_date_deb then        
        return 0;
    elsif (trunc(l_date_deb) = trunc(l_date_fin)) then        
	return (l_date_fin - l_date_deb)*24*60*60;    
    else
/* Le délai est calculé comme :
        1/ "délai écoulé le dernier jour" + "délai écoulé le 1er jour" + "délai écoulé pendant les jours entre".
        2/ date1 - date2 donne un résultat en nombre de jour (*24*60*60) pour avoir un résultat en seconde
        3/ to_date('19000101'||p_heure_fin,'YYYYMMDDhh24:mi')-to_date('19000101'||p_heure_debut,'YYYYMMDDhh24:mi') 
           ==> ici 0.5 car les journées ne font pas 24h mais seulement 12h
*/         
        return (l_date_fin - to_date(to_char(l_date_fin,'YYYYMMDD')||p_heure_debut,'YYYYMMDDhh24:mi'))*24*60*60
             + (to_date(to_char(l_date_deb,'YYYYMMDD')||p_heure_fin,'YYYYMMDDhh24:mi') - l_date_deb)*24*60*60
             + l_nb_jour_ouvre_entre * ( to_date('19000101'||p_heure_fin,'YYYYMMDDhh24:mi')
                                       - to_date('19000101'||p_heure_debut,'YYYYMMDDhh24:mi'))*24*60*60;
 
    end if;
 
end func_DureeSecJO;
/

Code:

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
SQL> WITH t AS (
  2      SELECT to_date('13/06/2012 18:15','dd/mm/yyyy hh24:mi') AS d_deb, to_date('16/06/2012 9:30','dd/mm/yyyy hh24:mi') AS d_fin FROM dual union ALL
  3      SELECT to_date('13/06/2012 18:15','dd/mm/yyyy hh24:mi') AS d_deb, to_date('14/06/2012 02:30','dd/mm/yyyy hh24:mi') AS d_fin FROM dual union ALL
  4      SELECT to_date('13/06/2012 18:15','dd/mm/yyyy hh24:mi') AS d_deb, to_date('13/06/2012 18:30','dd/mm/yyyy hh24:mi') AS d_fin FROM dual union ALL
  5      SELECT to_date('13/06/2012 22:15','dd/mm/yyyy hh24:mi') AS d_deb, to_date('16/06/2012 09:30','dd/mm/yyyy hh24:mi') AS d_fin FROM dual union ALL
  6      SELECT to_date('31/10/2012 18:00','dd/mm/yyyy hh24:mi') AS d_deb, to_date('01/11/2012 09:30','dd/mm/yyyy hh24:mi') AS d_fin FROM dual union ALL
  7      SELECT to_date('31/10/2012 20:00','dd/mm/yyyy hh24:mi') AS d_deb, to_date('01/11/2012 05:30','dd/mm/yyyy hh24:mi') AS d_fin FROM dual union ALL
  8      SELECT to_date('01/11/2012 12:00','dd/mm/yyyy hh24:mi') AS d_deb, to_date('02/11/2012 07:30','dd/mm/yyyy hh24:mi') AS d_fin FROM dual union ALL
  9      SELECT to_date('03/11/2012 12:00','dd/mm/yyyy hh24:mi') AS d_deb, to_date('04/11/2012 07:30','dd/mm/yyyy hh24:mi') AS d_fin FROM dual union ALL
 10      SELECT to_date('13/06/2012 22:15','dd/mm/yyyy hh24:mi') AS d_deb, to_date('14/06/2012 05:30','dd/mm/yyyy hh24:mi') AS d_fin FROM dual
 11      )
 12      SELECT d_deb, d_fin,
 13             (SELECT func_DureeSecJO(d_deb, d_fin) FROM dual) AS dureesec
 14       FROM t;
13/06/2012 18:15:00 16/06/2012 09:30:00      89100
13/06/2012 18:15:00 14/06/2012 02:30:00       2700
13/06/2012 18:15:00 13/06/2012 18:30:00        900
13/06/2012 22:15:00 16/06/2012 09:30:00      86400
31/10/2012 18:00:00 01/11/2012 09:30:00       3600
31/10/2012 20:00:00 01/11/2012 05:30:00          0
01/11/2012 12:00:00 02/11/2012 07:30:00       1800
03/11/2012 12:00:00 04/11/2012 07:30:00          0
13/06/2012 22:15:00 14/06/2012 05:30:00          0
 
9 rows selected.
 
SQL>

Toujours à bien tester avant utilisation...

08/11/2012, 00h21
lllhuhdklll

Je ne cite pas pour ne pas faire trop de lignes.

Merci, je teste demain et je redirais si ça fonctionne correctement.
08/11/2012, 23h04
lllhuhdklll

Bien, juste un message pour dire que ça fonctionne parfaitement.

Merci.

une methode faite autrement avec résultats verifiés en manuel avec des données proposées par skuatamad .

Code:

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
WITH t AS 
(
 
        SELECT 1 id,to_date('13/06/2012 18:15','dd/mm/yyyy hh24:mi') AS d_deb, to_date('16/06/2012 09:30','dd/mm/yyyy hh24:mi') AS d_fin  FROM dual union ALL
        SELECT 2   ,to_date('13/06/2012 18:15','dd/mm/yyyy hh24:mi') AS d_deb, to_date('14/06/2012 02:30','dd/mm/yyyy hh24:mi') AS d_fin FROM dual union ALL
        SELECT 3   ,to_date('13/06/2012 18:15','dd/mm/yyyy hh24:mi') AS d_deb, to_date('13/06/2012 18:30','dd/mm/yyyy hh24:mi') AS d_fin FROM dual union ALL
        SELECT 4   ,to_date('13/06/2012 22:15','dd/mm/yyyy hh24:mi') AS d_deb, to_date('16/06/2012 09:30','dd/mm/yyyy hh24:mi') AS d_fin FROM dual union ALL
        SELECT 5   ,to_date('31/10/2012 18:00','dd/mm/yyyy hh24:mi') AS d_deb, to_date('01/11/2012 09:30','dd/mm/yyyy hh24:mi') AS d_fin FROM dual union ALL
        SELECT 6   ,to_date('31/10/2012 20:00','dd/mm/yyyy hh24:mi') AS d_deb, to_date('01/11/2012 05:30','dd/mm/yyyy hh24:mi') AS d_fin FROM dual union ALL
        SELECT 7   ,to_date('01/11/2012 12:00','dd/mm/yyyy hh24:mi') AS d_deb, to_date('02/11/2012 07:30','dd/mm/yyyy hh24:mi') AS d_fin FROM dual union ALL
        SELECT 8   ,to_date('03/11/2012 12:00','dd/mm/yyyy hh24:mi') AS d_deb, to_date('04/11/2012 07:30','dd/mm/yyyy hh24:mi') AS d_fin FROM dual union ALL
        SELECT 9   ,to_date('13/06/2012 22:15','dd/mm/yyyy hh24:mi') AS d_deb, to_date('14/06/2012 05:30','dd/mm/yyyy hh24:mi') AS d_fin FROM dual
)
 
 
SELECT id,
((trunc(d_fin)-trunc(d_deb)-1)
 
+
 
case when to_date(trunc(d_deb,'dd')||' 19:00','dd/mm/yy hh24:mi')-d_deb>0 then to_date(trunc(d_deb,'dd')||' 19:00','dd/mm/yy hh24:mi')-d_deb else 0end*2
+
 
case when d_fin-to_date(trunc(d_fin,'dd')||' 07:00','dd/mm/yy hh24:mi')>0 then d_fin-to_date(trunc(d_fin,'dd')||' 07:00','dd/mm/yy hh24:mi') else 0end*2
 
)*12*60*60
from t

resultat:

Code:

verification:

Code:

1
2
3
4
5
6
7
8
9
10

		                                                   En minutes				
d_deb	 		d_fin	
					13/06/2012	14/06/2012	15/06/2012	16/06/2012	Total en sec
13/06/2012 18:15	16/06/2012 09:30	45		720		720		150	98100
13/06/2012 18:15	14/06/2012 02:30	45							2700
13/06/2012 18:15	13/06/2012 18:30	15							900
13/06/2012 22:15	16/06/2012 09:30	0		720		720		150	95400
13/06/2012 22:15	14/06/2012 05:30	0		0					0

Code:

1
2
3
4
5
6
7
	                                                                 En minutes					
d_deb			d_fin	
					31/10/2012	01/11/2012	02/11/2012	03/11/2012	04/11/2012	Total en sec
31/10/2012 18:00	01/11/2012 09:30	60	150								12600
31/10/2012 20:00	01/11/2012 05:30	0	0								0
01/11/2012 12:00	02/11/2012 07:30			420	30						27000
03/11/2012 12:00	04/11/2012 07:30				420		30				27000

j'attend vos commentaires.