Query Problem - including GROUP BY time() acts weird?

Store
,
1

In InfluxDB I have the following schema:

SHOW TAG KEYS
~
OrderStatus
tagKey
DB
Datacentre
Environment
StatusId
host

Then if I query the data with this, it give me expected results:

SELECT mean(“Count”) FROM “OrderStatus” WHERE time > now() - 1h GROUP BY “StatusId” fill(null)
~
OrderStatus
StatusId:123
time mean
2017-05-04T10:16:10.373940325Z 212
OrderStatus
StatusId:234
time mean
2017-05-04T10:16:10.373940325Z 987
…

However, when the data is queried with a “time(x)” in the GROUP BY clause, I get nothing :dizzy_face::

SELECT mean(“Count”) FROM “OrderStatus” WHERE time > now() - 1h GROUP BY time(60s), “StatusId” fill(null)
~
Success! (no results to display)

I can’t figure out why this might be happening!? What am I doing wrong? What’s happening to the data?

2

I think I found out an intermediary problem (still not the root cause) which might have been causing the problem above.

I’m receiving data every 2 minutes:

SELECT Count, StatusId FROM “OrderStatus” WHERE time > now() - 15m
~
OrderStatus
time Count StatusId
2017-05-04T12:40:00.114Z 2 “123”
2017-05-04T12:42:00.099Z 2 “123”
2017-05-04T12:44:00.11Z 2 “123”
2017-05-04T12:46:00.116Z 2 “123”
2017-05-04T12:48:00.105Z 2 “123”
2017-05-04T12:50:00.098Z 2 “123”

However, when I GROUP BY time(120s) most of the time buckets are empty!?

SELECT mean(“Count”) FROM “OrderStatus” WHERE time > now() - 15m GROUP BY time(120s), “StatusId” fill(null)
~
OrderStatus
StatusId:123
time mean
2017-05-04T11:40:00Z
2017-05-04T11:42:00Z 2
2017-05-04T11:44:00Z
2017-05-04T11:46:00Z
2017-05-04T11:48:00Z
2017-05-04T11:50:00Z

Why is the aggregation failing to select anything in most time buckets?

I even tried GROUP BY time(240s) to make sure each time buckets encompasses on average 2 data points and guaranteedly always at least one, but I am still returned empty for most time buckets.

3

Noticed something interesting - data was being shifted by one hour:

SELECT Count, StatusId FROM “OrderStatus” WHERE time > now() - 15m
~
OrderStatus
time Count StatusId
2017-05-04T 12:40:00.114Z 2 “123”

SELECT mean(“Count”) FROM “OrderStatus” WHERE time > now() - 15m GROUP BY time(120s), “StatusId” fill(null)
~
OrderStatus
StatusId:123
time mean
2017-05-04T 11:40:00Z

I don’t think this is supposed to happen!?

4

Found the problem … did I ?

Posted at 12:57pm UK local time (thus data points were 1h ahead of Universal Time).
Thus, the DB time 12:40:00 meant 13:40:00, meaning I was uploading the data 1h into the future.
Thus the “now() -15m” was picking up now -15m all the way to 1h into the future.

Still … not sure there’s not still something weird going on. I was running both queries seconds apart, against the same exact data. How is it that one query would return times 1h in the future (as expected per original data) while the GROUP BY query wasn’t?