Hi everybody… I have a data with the following structure:
| table_result | _measurementgroupstring | _fieldgroupstring | _valueno groupdouble | _startgroupdateTime:RFC3339 | _stopgroupdateTime:RFC3339 | _timeno groupdateTime:RFC3339 | domaingroupstring | entity_idgroupstring | friendly_namegroupstring | sourcegroupstring |
|---|---|---|---|---|---|---|---|---|---|---|
| 0 | units | value | 1 | 2024-02-19T13:34:13.227Z | 2024-02-19T19:34:13.227Z | 2024-02-19T13:47:22.334Z | binary_sensor | teste1_person_occupancy | Teste1 person occupancy | HA |
| 0 | units | value | 0 | 2024-02-19T13:34:13.227Z | 2024-02-19T19:34:13.227Z | 2024-02-19T13:47:39.649Z | binary_sensor | teste1_person_occupancy | Teste1 person occupancy | HA |
| 0 | units | value | 0 | 2024-02-19T13:34:13.227Z | 2024-02-19T19:34:13.227Z | 2024-02-19T13:48:39.956Z | binary_sensor | teste1_person_occupancy | Teste1 person occupancy | HA |
| 0 | units | value | 1 | 2024-02-19T13:34:13.227Z | 2024-02-19T19:34:13.227Z | 2024-02-19T13:49:12.955Z | binary_sensor | teste1_person_occupancy | Teste1 person occupancy | HA |
| 0 | units | value | 0 | 2024-02-19T13:34:13.227Z | 2024-02-19T19:34:13.227Z | 2024-02-19T13:49:24.112Z | binary_sensor | teste1_person_occupancy | Teste1 person occupancy | HA |
| 0 | units | value | 1 | 2024-02-19T13:34:13.227Z | 2024-02-19T19:34:13.227Z | 2024-02-19T13:50:09.826Z | binary_sensor | teste1_person_occupancy | Teste1 person occupancy | HA |
I want to generate a table with how much time the 1 stayed 1… In better words, how much time since it became value 1 until the next 0.
Example:
entity_id|date|time_stayied_as_one
| entity_id | date | time_stayied_as_one |
|---|---|---|
| teste1_person_occupancy | 2024-02-19T13:47:22.334Z | 00:00:17 |
| teste1_person_occupancy | 2024-02-19T13:57:22.334Z | 00:05:00 |
And other aggregations such as by hour:
| entity_id | hour | time_stayied_as_one |
|---|---|---|
| teste1_person_occupancy | 12:00 - 13:00 | 00:05:17 |
| teste1_person_occupancy | 13:00 - 14:00 | 00:04:30 |
And/Or following the filter selected on InfluxDB and Grafana (last 6 horas i.e.):
| entity_id | time_stayied_as_one |
|---|---|
| teste1_person_occupancy | 00:55:43 |
Is it possible to do in InfluxDB? I’m on version 2.7.5.
