Group daily values by month to align with utility bill

Grafana InfluxDB
1

I’m trying to group my daily gas usage to reflect what is shown in my monthly utility bills which cycle starts on the 13th of most months

  • What Grafana version and what operating system are you using?
    containerized at grafana:latest with influxdb:1.8
  • What are you trying to achieve?
    Match grouping of daily values to a similar grouping as statements (from 13th to 12th)
  • How are you trying to achieve it?
    Time series or bar chart with GROUP BY = time (30d)
  • What happened?
    Groups by 30 days, but the total’s dont make sense. First 30d grouping shows sum of 17 while this is really the last 9 days of values.
  • What did you expect to happen?
    30d grouping to sum 30 days. Is there a better way to group by month from 13th to 12th of every month?
  • Can you copy/paste the configuration(s) that you are having problems with?
    SELECT sum("value") FROM "therms" WHERE ("entity_id" = 'pge_daily_gas_usage') AND $timeFilter GROUP BY time($__interval) fill(none) tz('America/Los_Angeles')

Charts shown here as bar charts just so values are visible for each day here. I’d prefer to run
I’ve checked the daily values in influx and they match when the summing period is correct.
This is the statement data from the utility:
image

Chart I’m working on:

  • Why does this charts 30d group around the 19th through 24th? Is there a way to instruct or adjust when this happens?
  • How can I group by month from 13th to 12th of every month?

image
image3604×2378 393 KB

Changing it to daily grouping, looking at the last month:

2

I do not believe there is a way in InfluxQL to group by month using a specific date range (e.g. start on 13th, end on 12th of following month).

If you are willing to change to & learn Flux, then I think simply aggregating by month with an offset value of 13 days would do the trick:

 |> aggregateWindow(every: 1mo, offset: 13d, fn: sum, timeSrc:"_start")

More here. Using the offset parameter shifts the start of monthly windows to the desired day.

1 Like