# Calculate time between a value change

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.

Welcome @hawkbraz

Did you read / try this?

1 Like

No, I didn’t found earlier… But thank you it was really helpful, I’ll try at morning and give you a feedback!