Re: Requète SQL

Top Page

Reply to this message
Author: sxpert
Date:  
To: guilde
Subject: Re: Requète SQL
On 2012-12-29 22:52, Frédéric wrote:
> On samedi 29 décembre 2012, Xavier Belanger wrote:
>
>> Recherche effectuée, MySQL intègre une fonction pour comparer des
>> valeurs horodatées, 'timediff' :
>>
>> [
>>
>> http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#functi
>> on_timediff ]
>
> Ah, intéressant !
>
>> Par contre, obtenir directement le temps écoulé dans la requête
>> précédente
>> demandera de construire une autre requête intégrée.
>
> Yep. Mais pour l'instant, je ne vois pas comment m'y prendre...


bon... ca se fait avec un vrai sgbdr, je sais pas avec mysql ;)
*hint*postgres*hint*

note : j'ai ajouté une paire d'événements pour le test ;)

drop table test;

create table test (
         ts      timestamp default CURRENT_TIMESTAMP,
         object  varchar(256),
         value   varchar(256)
);


insert into test (ts, object, value) values
         ('2012-12-29 07:29:40','heating_cmd_sdb_rdc',           'on'),
         ('2012-12-29 07:40:18','temp_sdb_rdc',                  
'20.24'),
         ('2012-12-29 07:51:16','temp_degagement_ouest',         
'20.48'),
         ('2012-12-29 08:00:00','heating_cmd_sdb_rdc',           'off'),
         ('2012-12-29 08:00:00','heating_setpoint_sejour',       '20'),
         ('2012-12-29 08:00:30','heating_cmd_sdb_rdc',           'on'),
         ('2012-12-29 08:02:10','temp_cuisine',                  
'20.8'),
         ('2012-12-29 08:08:15','temp_entree',                   
'20.16'),
         ('2012-12-29 08:09:38','temp_salon_screen',             
'20.41'),
         ('2012-12-29 08:10:06','temp_bureau_porte',             
'20.4'),
         ('2012-12-29 08:10:15','temp_sdb_rdc',                  
'20.4'),
         ('2012-12-29 08:15:15','temp_sdb_rdc',                  
'20.48'),
         ('2012-12-29 08:20:14','temp_sdb_rdc',                  
'20.64'),
         ('2012-12-29 08:25:14','temp_sdb_rdc',                  
'20.8'),
         ('2012-12-29 08:28:22','temp_cuisine_plan_travail',     
'20.48'),
         ('2012-12-29 08:30:13','temp_sdb_rdc',                  
'20.88'),
         ('2012-12-29 08:35:13','temp_sdb_rdc',                  
'20.96'),
         ('2012-12-29 08:39:15','temp_salon_ouest',              
'20.48'),
         ('2012-12-29 08:40:12','temp_sdb_rdc',                  
'21.04'),
         ('2012-12-29 08:45:12','temp_sdb_rdc',                  
'21.2'),
         ('2012-12-29 08:48:24','temp_cuisine_plan_travail',     
'20.56'),
         ('2012-12-29 08:48:55','heating_cmd_sdb_rdc',           'off'),
         ('2012-12-29 08:49:15','temp_salon_ouest',              
'20.56'),
         ('2012-12-29 08:49:36','temp_salon_screen',             
'20.47'),
         ('2012-12-29 08:50:11','temp_sdb_rdc',                  
'21.28');


create or replace view cmd_events as select * from test where object 
like 'heating_cmd%' order by ts;
create view on_evt as select * from cmd_events where value = 'on';
create view off_evt as select * from cmd_events where value = 'off';
select distinct on (on_evt.ts) * from on_evt, (select * from off_evt) 
as off_evt where on_evt.ts<off_evt.ts ;
          ts          |       object        | value |         ts         

|       object        | value

---------------------+---------------------+-------+---------------------+---------------------+-------
  2012-12-29 07:29:40 | heating_cmd_sdb_rdc | on    | 2012-12-29 
08:00:00 | heating_cmd_sdb_rdc | off
  2012-12-29 08:00:30 | heating_cmd_sdb_rdc | on    | 2012-12-29 
08:48:55 | heating_cmd_sdb_rdc | off
(2 rows)


>> si nous supprimons le premier on nous avons :

  select distinct on (on_evt.ts) * from on_evt, (select * from off_evt) 
as off_evt where on_evt.ts<off_evt.ts ;
          ts          |       object        | value |         ts         

|       object        | value

---------------------+---------------------+-------+---------------------+---------------------+-------
  2012-12-29 08:00:30 | heating_cmd_sdb_rdc | on    | 2012-12-29 
08:48:55 | heating_cmd_sdb_rdc | off
(1 row)


>> si nous supprimons le 2e off nous avons :

test=# select distinct on (on_evt.ts) * from on_evt, (select * from 
off_evt) as off_evt where on_evt.ts<off_evt.ts ;
          ts          |       object        | value |         ts         

|       object        | value

---------------------+---------------------+-------+---------------------+---------------------+-------
  2012-12-29 07:29:40 | heating_cmd_sdb_rdc | on    | 2012-12-29 
08:00:00 | heating_cmd_sdb_rdc | off
(1 row)


>> en ayant le off du milieu et le on du milieu :

test=# select distinct on (on_evt.ts) * from on_evt, (select * from
off_evt) as off_evt where on_evt.ts<off_evt.ts ;
ts | object | value | ts | object | value
----+--------+-------+----+--------+-------
(0 rows)