Query InfluxDB for specific hours every day

Question

What is the best way to query InfluxDB for specific hours every day, for example, I have a Series that have checkin/checkout activities, and I need to see them between hour 2PM - 3PM every day for last month, am aware that there's no direct way to do this on the query language -current version 1.2- Not sure if there is a work around or something ?

Answer 1

I have been searching for the same and found your question. As you say, the syntax does not seem to allow to do it.

My closest attempt was trying to use a regular expression for a time WHERE clausule, which is not currently supported by InfluxDB.

So that should probably be the answer, and I would not post an answer to just say that.

However, working on a different problem, I have found a way that may or may not help you in your specific case. It is a workaround that is not very nice, but it seems to work in the case that you can formulate an aggregation/selection of what you want to see in that given hour so that you end up with having one value per hour. For example, (mean/max/count number of checkin-checkouts in that hour for a given person, which may be what you are looking for, or that you may use to identify the days that you would like to them individually query to see what happened there).

For example, I want to obtain the measurement of electricity consumption daily from 00:00 to 06:00 a.m. I make a first subquery that divides the measurements grouping by 6 hours starting at 00:00 of a given date. Then in the main query, I group by 24 hours and I select the first value. Like this

SELECT first("mean") FROM (SELECT mean("value") FROM "Energy" WHERE "devicename" = 'Electricity' AND "deviceid" = '0_5'  AND time > '2017-01-01' GROUP BY time(6h) ) WHERE time > '2017-01-01' GROUP BY time(24h)

If you want 2-4 pm, so 14:00-16:00, you need to first group by 2 hours in the subquery, then offseting the set by 14h so that it starts at 14:00.

SELECT first("mean") FROM ( SELECT mean("value") FROM "Energy" WHERE "devicename" = 'Electricity' AND "deviceid" = '0_5'  AND time > '2017-01-01T14:00:00Z' GROUP BY time(2h) ) WHERE time > '2017-01-01T14:00:00Z' GROUP BY time(24h,14h)

Just for checking it. In my 1.2 InfluxDB this is the final result:

Energy
time    first
2017-01-01T14:00:00Z    86.41747572815534
2017-01-02T14:00:00Z    43.49722222222222
2017-01-03T14:00:00Z    81.05416666666666

The subquery returns:

Energy
time    mean
2017-01-01T14:00:00Z    86.41747572815534
2017-01-01T16:00:00Z    91.46879334257974
2017-01-01T18:00:00Z    89.14027777777778
2017-01-01T20:00:00Z    94.47434119278779
2017-01-01T22:00:00Z    89.94305555555556
2017-01-02T00:00:00Z    86.29542302357837
2017-01-02T02:00:00Z    92.2625
2017-01-02T04:00:00Z    89.93619972260748
2017-01-02T06:00:00Z    87.78888888888889
2017-01-02T08:00:00Z    50.790277777777774
2017-01-02T10:00:00Z    0.6597222222222222
2017-01-02T12:00:00Z    0.10957004160887657
2017-01-02T14:00:00Z    43.49722222222222
2017-01-02T16:00:00Z    86.0610263522885
2017-01-02T18:00:00Z    86.59778085991678
2017-01-02T20:00:00Z    91.56527777777778
2017-01-02T22:00:00Z    90.52565880721221
2017-01-03T00:00:00Z    86.79166666666667
2017-01-03T02:00:00Z    87.15533980582525
2017-01-03T04:00:00Z    89.47988904299584
2017-01-03T06:00:00Z    91.58888888888889
2017-01-03T08:00:00Z    41.67732962447844
2017-01-03T10:00:00Z    16.216366158113733
2017-01-03T12:00:00Z    25.27739251040222
2017-01-03T14:00:00Z    81.05416666666666

If you would need 13:00-15:00, you need to offset the subquery in the previous example by 1h.

For 14:00-15:00:

SELECT first("mean") FROM ( SELECT mean("value") FROM "Energy" WHERE "devicename" = 'Electricity' AND "deviceid" = '0_5'  AND time > '2017-01-01T14:00:00Z' GROUP BY time(1h) ) WHERE time > '2017-01-01T14:00:00Z' GROUP BY time(24h,14h)

Hope this helps :)