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)
