December 31, 2018, 4:45am
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.
December 31, 2018, 8:46am
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
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”
December 31, 2018, 9:41am
I suggest turning to a SQL forum…
December 31, 2018, 12:10pm
persistence and maybe
December 31, 2018, 1:42pm
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, …
December 31, 2018, 3:11pm
Thanks, I’ll probably go this way