Is it possible to query for all values written at 6am?

1

We’ve been writing point to Influx every fifteen minutes for some time now, and we’d like to see what all the values were at, say, 6am every day.

I flipped through the docs and didn’t find anything that can do time functions. Only idea I’ve had so far is to re-write all of our values back to the DB and tag them with “hour” and “minute” This seems a little verbose and un-necessary.

Any better way?

2

@gmoore Well you can always query specifically for those times with time literals in UTC using a WHERE clause. Most people will just take a MEAN() of the day and return those. Do either of those solutions work for you?

3

That’s what I’m missing. How do I do that?

select * from my_measurement where time=(6am)

Just to be clear by way of example, we’re looking for: all values taken at 6am in April.

Is this possible in a single query? Or, do we have to make 30 calls, one for each day in April?

4

@gmoore One for each day in April. Why are you specifically targeting the 6AM values? Is this something that can’t be caught by an aggregate query?

5

@gmoore So I think this might be possible with single query. It’s a little wonky though.

select first(*) from mymeas where time > '2017-04-01' group by time(1d, 6h)
6

Indeed it works!

time                 first_value
----                 -----------
2017-04-06T06:00:00Z 0.3
2017-04-07T06:00:00Z 2.76
2017-04-08T06:00:00Z 2.34
2017-04-09T06:00:00Z 2.02

I thought there was a way through using group by but couldn’t figure it out. Thanks!