I’m trying to setup a time series graph that has both a bar chart showing the total water usage per hour, as well as a line chart showing the flow rate over time (using TimeScaleDB/Postgres) if I aggregate both of these at 1 hour, I get a chart like this - which is sorta what I’m after but i’d like to aggegate the line chart by 1 min so it shows more information:
(yellow = total, green = flow rate)
If I change aggregation for the line to 1 min like I want, then this is the result, where now the bar graph only covers 1 minute also with large gaps in between
Here are my queries for each:
Flow Rate:
WITH t as (
SELECT
time_bucket('1 minute'::interval, ts) as time,
time_weight('LOCF', ts, val_real) AS tw -- get a time weight summary
FROM historian
WHERE $__timeFilter(ts) AND tag = 'FIT_1_FLOW_RATE'
GROUP BY time_bucket('1 minute'::interval, ts)
)
SELECT
time,
average(tw) AS flow_rate -- extract the average from the time weight summary
FROM t;
Total Flow:
WITH t as (
SELECT
time_bucket('1 hour'::interval, ts) as time,
time_weight('LOCF', ts, val_real) AS tw -- get a time weight summary
FROM historian
WHERE $__timeFilter(ts) AND tag = 'FIT_1_FLOW_RATE'
GROUP BY time_bucket('1 hour'::interval, ts)
)
SELECT
time,
average(tw)*60 AS flow_total -- extract the average from the time weight summary
FROM t;
Is this something grafana can do? I can’t seem to find any way to get these to show together like I’m after.
