How to aggregate data by week or by month using SQL

InfluxDB IOx
,
1

I have this query running fine in the FlightSQL plugin for Grafana which shows the electricity demand by hour (the data is collected every hour and the value represents the electricity demand for that hour):

SELECT * FROM "ElectricPowerOperations"
 WHERE "type" IN ('Demand')
 AND "region" IN ('Texas') AND $__timeRange(time)

image
image1309Ă—573 52.5 KB

How can I modify the above query to produce a graph that aggregates the data by week and another graph that aggregates by month?

In Flux, I would do this to get the data by week:

  |> aggregateWindow(every: 1w, offset: -3d, fn: sum)  // sums the 7 days of each week to get the total used per week, and use -3d as the offset to move the weekly aggregate to start on a Monday

and this to get the data by month:

  |> aggregateWindow(every: 1mo, fn: sum)  // sums the total used per month

How is this done in SQL?

2

@grant1 you are succumbing to the SQL side??! :scream::pensive:

3

With the help of the inimitable @Jay_Clifford , here is how this is done:

Flux aggregated by month:

from(bucket: "EIAtest7")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "ElectricPowerOperations")
  |> filter(fn: (r) => r["_field"] == "value")
  |> filter(fn: (r) => r["type"] =~ /^${type:regex}$/ )
  |> filter(fn: (r) => r["region"] =~ /^${region:regex}$/ )
  |> group(columns: ["region", "_measurement"], mode:"by") 
  |> aggregateWindow(every: 1mo, offset: 1d, fn: sum, timeSrc:"_start")  // sums the total used per month
  |> yield(name: "monthly_data")

image
image1583Ă—414 36.7 KB

SQL using date_Bin with 1 month interval:

SELECT date_Bin(INTERVAL '1 month', time, TIMESTAMP '1970-01-01 00:00:00Z') AS time, sum("value") AS sum_value, region
 FROM "ElectricPowerOperations" WHERE "type" IN ('Demand') AND "region" IN (${region}) AND $__timeRange(time) GROUP BY 1, region
 ORDER BY time ASC

image
image1583Ă—414 34.4 KB

Not sure exactly why the values from the two graphs are not identical…probably something to do with the aggregateWindow() function in Flux.

1 Like