Grouping by hour ignoring the date?

[jms3000]

I have hourly energy measurements from home assistant (influxdb).
I want to display them in a graph having a x-axis from 0:00 to 24:00 and the date should be ignored. So the mean value of all energy measurements from 10 o’clock to 11:00 o’clock from the whole year should appear at the 11:00 line. Does sth. like “group by hour(time)” work?

SELECT "value" FROM "sensor.total_energy_hourly_copy"  GROUP BY ?

[pooh]

You might be able to achieve this, but it depends very much on which back-
end data store you are using. It the query language for that supports the
hour() function, then you can probably combine this with curdate() (or
whatever the equivalent is) and order to generate the $time value passed to
Grafana.

If your back-end data store query language can’t do it, though, then I’m
pretty certain Grafana can’t help.

Antony.

[jms3000]

The database is influx and it supports only time(1h) which is rounded to the hour, but with date.
I thought the time series display can do that in grafana.

[lyve1981]

@jms3000 have you ever been able to resolve this? I have the exact same problem.

I want to visualize my hourly energy consumption as an average for a month. At the moment, I have the hourly consumption, but individually for each day, like this:

Now I’d like to sum the hours for all days but I don’t know how to do that. I also use InfluxDB and my current query is this:

SELECT difference(last("value_float")) FROM "mqtt" WHERE ("topic" = 'mains/kwh') AND $timeFilter GROUP BY time(1h)

Could anyone help?

[jms3000]

I have written a python program reading the data and creating the graph.

[alexandrearmand]

I think one simple way to think about it is you want your date column to have field who can be a unique key to group by.
To do that i suppose there is some fuction to format date or atleast format string to keep only a date with day format without hour.

SELECT sum(value) ,substr(string(tim_column),0,9) as time FROM "mqtt" WHERE ("topic" = 'mains/kwh') AND $timeFilter GROUP BY time

you can even imagine make a variable of this split duration, to be able to change aggregation window time
btw this is one line in Flux or influxdb 2.0

[lyve1981]

Hi,

thank you for your answer. Unfortunately InfluxQL doesn’t allow to manipulate the time string-based. However, I now added a Flux based data source.

Could you share some insight how this one-liner should look like?

Thanks!