Hello, I’m trying to calculate the time when my switch was ON for the last month using data from MySQL table for specific switch. The table looks like this.
Can someone suggest the best way on doing this. I’m using 3rd party reporting tool so I can only run SQL queries to database to calculate it.
Hello @goopilot,
From what I can see it looks pretty complicated to extract some valuable data from this.
ON -> OFF is doable but this:
ON -> OFF -> OFF -> OFF
or
ON -> ON
is a bit confusing right ?
I wanted to get moreless the same information than you and solved it by using “grafana + influxdb”
It generates some graphs where I can see from when to when the switch were on or off visually. But not directly “how long it was turned on or off”
I suggest turning to a SQL forum…
I personally use persistence with InfluxDB and it works great for this kind of stuff. The query language is perfect for time series and you can easily “downsample”, e.g. create time series in which the time interval is fixed (like every 1 hour, or every 1 day, etc…) and the measure can be avg, sum, max, …
Thanks, I’ll probably go this way
