MySQL - Calculate downtime of a motor based on power values

Question

I would like to ask for your support with the following.

I would like to calculate the daily downtime (Power = 0) of a motor with a MySQL query. The table structure and sample of my dataset - below - has only timestamp and motor power. The data sampling rate is 10 seconds.

Table structure (Datum Timestamp, Pot int);

Data set sample | Timestamp |Power| | --------- | --- | | 2023-10-06 21:43:40 | 474 | | 2023-10-06 21:43:50 | 0 | | 2023-10-06 21:45:40 | 0 | | 2023-10-06 21:45:50 | 40 | | 2023-10-06 21:53:50 | 245 | | 2023-10-06 21:54:00 | 0 | | 2023-10-07 09:15:40 | 0 | | 2023-10-07 09:15:50 | 2 | | 2023-10-07 09:28:50 | 322 | | 2023-10-07 09:29:00 | 0 | | 2023-10-07 09:30:40 | 0 | | 2023-10-07 09:30:50 | 9 | | 2023-10-07 12:59:10 | 471 | | 2023-10-07 12:59:20 | 0 | | 2023-10-07 13:42:20 | 0 | | 2023-10-07 13:42:30 | 82 |

I created my own query, but it only managed to calculate with the daily max and the min timestamps. Hence, the downtimes happening within the day were - not on purpose - ignored. It also ignored the date change between the 6th. and the 7th. (the fiddle is here: https://dbfiddle.uk/Z3K4CIRR ) When I use a cloud-based analysis service, the result of the same query is Date Daily Downtime [%] Daily Uptime [%] 2023-10-06 9 91 2023-10-07 57 43 which is in any case wrong

I have tried adapting my data to a customized version of these solutions; however, the result I get is quite different from the manually calculated downtimes.

1. how to calculate time for engine on and off in mysql
2. Calculate downtime based on status 'UP' / 'DOWN'
3. Find downtimes and calculate lengths of downtime
4. calculate downtime based on status values

Moreover, most of the datasets in the solutions have a “Status” somewhere; I have a power value.

It is also possible I have not fully “digested” the solutions and I have somewhere a mistake (highly probable, as I am a newbie in this topic)

I would like to get the following result for the sample data shown above:

Date	Downtime	Daily downtime [%]
2023-10-06	02:07:50	8,9
2023-10-07	10:00:20	42

With this result I could carry on with other KPI’s of the motor.

Thanks a lot! MySQL GreenBoy