So I am adding a new panel is simply trying to track how long a machine is up/running during a shift my data is stored in AWS Timestream. I have 3 queries one per shift, I have just shown the second shift but they all have the same issue. I really don’t understand what’s going on so I am not really able to troubleshoot it well. My goal would be to have 3 bars side by side that show 0-8 hours (no bar for zero) on a given day. So my query still has editing to make it right but right now the data doesn’t visualize well at all.
The following query will produce:
SELECT BIN(time, 8h) AS binned_timestamp, CASE
WHEN hour(BIN(time, 8h)) = 16
THEN round(((max(measure_value::double)-Min(measure_value::double))/3600))
WHEN BIN(time, 8h) = null
THEN 0
ELSE 0
END AS “2nd”
from “Main”.“CMC_107”
where measure_name = ‘SID1.CSOpHr.Value’
group by BIN(time, 8h)
ORDER BY binned_timestamp desc
|time |Value|
|2021-03-17 16:00:00.000000000|4.0|
|2021-03-17 08:00:00.000000000|0.0|
|2021-03-17 00:00:00.000000000|0.0|
|2021-03-16 16:00:00.000000000|8.0|
|2021-03-16 08:00:00.000000000|0.0|
|2021-03-15 16:00:00.000000000|1.0|
|2021-03-15 08:00:00.000000000|0.0|
|2021-03-11 16:00:00.000000000|6.0|
|2021-03-11 08:00:00.000000000|0.0|
|2021-03-10 16:00:00.000000000|4.0|
My data shows up as follows:
