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.
- how to calculate time for engine on and off in mysql
- Calculate downtime based on status 'UP' / 'DOWN'
- Find downtimes and calculate lengths of downtime
- 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