Group by time doesn't work at all

1

Hi,

I have some test data in the DB and can select something like this:

SELECT value FROM nkktable.autogen.NKK WHERE time > now() - 1d GROUP BY position

where I get 10000 rows back, like

…
1580982247997500000 0.07770824505372763
1580982247997600000 0.05979153569768511
1580982247997700000 -0.2742447279826496
1580982247997800000 -0.1899619011087859
…

If I change the select to

SELECT mean(value) FROM nkktable.autogen.NKK WHERE time > now() - 1d GROUP BY time(1s),position

the result is empty. The data are created a few minutes ago with current timestamp.

What am I doing wrong?

2

Check the whole dataset, you may get lots of empty rows since “GROUP BY 1s” actually force the result to show the data for each second, if a second has no data the row will be empty.

If your data are of few minutes ago your dataset will be populated only at the end of the filtered interval.

sample:

SELECT mean(value) {...] where time > X GROUP BY time(1s)

let's say the first data point is at the moment X+5

I will get
time | mean(value)
X	 | 
X+1	 | 
X+2	 | 
X+3	 | 
X+4	 | 
X+5	 | 10
3

Hmmm …

This is what I get:

D:\bin\influxdb-1.7.9-1>influx -execute “SELECT value FROM nkktable.autogen.NKK WHERE
time > now() - 1d GROUP BY position” -database=“nkktable” -precision “rfc3339”
…
2020-02-06T10:39:32.9989Z -0.11378374393646741
2020-02-06T10:39:32.999Z -0.11041258804645955
2020-02-06T10:39:32.9991Z 0.044214054710171244
2020-02-06T10:39:32.9992Z -0.26773165826639767
2020-02-06T10:39:32.9993Z 0.08495987770313235
2020-02-06T10:39:32.9994Z -0.07628870184047067
2020-02-06T10:39:32.9995Z -0.03996397807533887
2020-02-06T10:39:32.999599Z 0.07458517613288941
2020-02-06T10:39:32.9997Z -0.21276366875258923
2020-02-06T10:39:32.9998Z -0.21013295412211594
2020-02-06T10:39:32.9999Z -0.24776373854879136

D:\bin\influxdb-1.7.9-1>date/T
06.02.2020

D:\bin\influxdb-1.7.9-1>time/T
10:56

D:\bin\influxdb-1.7.9-1>influx -execute “SELECT mean(value) FROM nkktable.autogen.NKK
WHERE time > now() - 1d GROUP BY time(1h),position” -database=“nkktable” -precision
“rfc3339”

D:\bin\influxdb-1.7.9-1>

4

Do other aggregation functions work? like sum? (maybe de data is not a number, and therefore you can’t use mean, but I expect an error in that case)

check the field data type by using
SHOW FIELD KEYS ON

5

Ok, all of your suggestions didn’t help, but by chance I found the following behaviour:

With
D:\bin\influxdb-1.7.9-1>influx -execute “SELECT mean(value) FROM nkktable.autogen.NKK
WHERE time > now() - 1d and time < now()+1d GROUP BY time(1h),position” -database=“n
kktable” -precision “rfc3339”

I get the right results. With
D:\bin\influxdb-1.7.9-1>influx -execute “SELECT mean(value) FROM nkktable.autogen.NKK
WHERE time > now() - 1d GROUP BY time(1h),position” -database=“nkktable” -precision
“rfc3339”

I get nothing. So there seems to be a restriction for future values and some issue with time zone?!

6

By default, the timezone used by influx is UTC (docs here).
The function to set the timezone is tz(), and is placed at the end of the query

SELECT {...} tz('<time_zone>')

It is usually managed by the frontend tools themselves (Chronograf/Grafana), but since you are using the CLI you may need to specify it.

7

Sorry, but this doesn’t work for windows binaries.

Again: I can (now) deal with the timezone problem, but I’m confused that I have to set the upper time limit to get the result, but if I let the upper time limit free, I get no result.

This can only be a bug in my opinion.