requete SQL intersect/not exist

Version imprimable

bonjour, voici ma requête

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
 
   select
      pid_log, 
      jobname_log,
      dbtimestamp_log, 
      dbendstamp_log, 
      timediff(`dbendstamp_log`,`dbtimestamp_log`) AS Duree, 
      script_log, 
      args_log, 
      returncode_log
    from 
    log
    where true  
    AND message_log!='Wrapper End'
    AND dbtimestamp_log between '".$date2."' and  '".$date1."'
    UNION
    select
      pid_log, 
      jobname_log,
      dbtimestamp_log, 
      dbendstamp_log, 
      timediff(`dbendstamp_log`,`dbtimestamp_log`) AS Duree, 
      script_log, 
      args_log, 
      returncode_log
    from 
    log
    where true  
    AND message_log!='Wrapper End'
    AND dbtimestamp_log between '".$date3."' and  '".$date4."'
    order by dbtimestamp_log DESC,  returncode_log DESC
    ;

j'aimerai savoir comment la modifier afin que celle ci me donne au résultat seulement les lignes qui n'existent que pour les dates3/4 et ou dates1/2

pour résumer je veux le nom des jobs et des scripts qui ont tourné durant la première et qui n'ont pas tourné dans la 2e ou
qui on tourné dans la 2e nuit et pas la première

d'avance merci :mrgreen:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
select pid_log, jobname_log, dbtimestamp_log, dbendstamp_log, 
       timediff('dbendstamp_log','dbtimestamp_log') AS Duree, 
       script_log, args_log, returncode_log
from   log
where  message_log <> 'Wrapper End'
  AND  dbtimestamp_log between CAST('".$date2."' AS DATE) 
                           and CAST('".$date1."' AS DATE)
UNION  ALL
select pid_log, jobname_log, dbtimestamp_log, dbendstamp_log, 
       timediff('dbendstamp_log','dbtimestamp_log') AS Duree, 
       script_log, args_log, returncode_log
from   log
where  message_log <> 'Wrapper End'
  AND  dbtimestamp_log between CAST('".$date3."' AS DATE) 
                           and  CAST('".$date4."' AS DATE) 
ORDER  BY dbtimestamp_log DESC, returncode_log DESC

Sans la définition de vos tables et la sémantique associée, impossible de vous aider !

A +

infos manquante pour la requête

Merci de me préciser qu'il manquait des informations :p

détail de la table

Citation:

Table Name: log
column Name Datatype
id_log Integer
dbtimestamp_log Timestamp
timestamp_log Datetime
dbendstamp_log Timestamp
endstamp_log Datetime
returncode_log Integer
script_log varchar(200)
pid_log varchar(30)
jobname_log varchar(45)

définition des variables

Code:

1
2
3
4
$date1=date("Y-m-d 08:00:00");
$date2=date("Y-m-d 19:00:00", time()-3600*24);
$date3=date("Y-m-d 08:00:00", time()-3600*24);
$date4=date("Y-m-d 19:00:00", time()-3600*48);

requête 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
24
25
26
27
28
29
30
31
 
    select
      pid_log, 
      jobname_log,
      dbtimestamp_log, 
      dbendstamp_log, 
      timediff(`dbendstamp_log`,`dbtimestamp_log`) AS Duree, 
      script_log, 
      args_log, 
      returncode_log
    from 
    log
    where true  
    AND message_log!='Wrapper End'
    AND dbtimestamp_log between '".$date2."' and  '".$date1."'
    UNION
    select
      pid_log, 
      jobname_log,
      dbtimestamp_log, 
      dbendstamp_log, 
      timediff(`dbendstamp_log`,`dbtimestamp_log`) AS Duree, 
      script_log, 
      args_log, 
      returncode_log
    from 
    log
    where true  
    AND message_log!='Wrapper End'
    AND dbtimestamp_log between '".$date3."' and  '".$date4."'
    order by dbtimestamp_log DESC,  returncode_log DESC

j'espère que cela peut vous aider plus

d'avance merci