I have some data collected at regular intervals, and I can’t work out how to graph them as time-of-day. For example I want to look at a week’s worth of data and have all the values for 09:00 averaged to a single value for 9:00, all the 9:01 values from each day averaged to a single 9:01 value, and so on, resulting in a graph with no dates but showing times from 00:00 to 23:59 on the X-axis.
From this I hope to be able to see intra-day trends like (for example) at 9am the values start to increase, and at 5pm they decrease, so I can average a week or more’s data to see what times of the day different behaviour is more or less likely to happen.
When looking at the InfluxQL query functions I can only see how to group by time intervals, whereas I really want to take the timestamp, drop the date, and just look at the hour/minute component when doing the grouping. Sort of like “GROUP BY drop_date(time(1m))” if there was such a thing as drop_date().
In order to do so you need to keep (or better extrapolate) those info as a tag.
If you are collecting data using Telegraf have a look at the date processor, it allows you to keep the timestamp in a human readable format, and since you can specify the format you want, you can just keep “hh:mm”
Sample Config
[[processors.date]]
## New tag to create
tag_key = "hour_minute"
## Date format string, must be a representation of the Go "reference time"
## which is "Mon Jan 2 15:04:05 -0700 MST 2006".
date_format = "15:00"
In general, I suggest you keep/calculate what you need at gathering time because (afaik) what you ask is not possible using InfluxQL or Flux
Ah bummer, but thanks for the info. I’ve already collected the data for the last six months (writing it directly to Influx via the API in a NodeJS script) so it’s too late to go back and change it. I guess I’ll see if I can export it and find something a bit more capable than Influx.
