[solved] InfluxDB sql via Group By and Order By

#1

Hello,

How can I select data with group by and order by? Like mysql for example.

SQL example:

SELECT “probe”,“lastvalue”,“lastvalue_raw” FROM “test_traffic” GROUP BY “probe” ORDER BY “time” ASC

The “probe” field is string. Is this possible or not?

regards

#2

Yes.

In fact the exact syntax you have quoted works fine for me.

Do you get an error? If so, what is it?

Antony.

#3

The group by is not working correct. He should group by “probe” and the grouped sort by time.

#4

The group by is not working correct. He should group by “probe” and the
grouped sort by time.

Here’s what I get for an almost identical query (obviously, I don’t have the
same data as you):

select callid from mqtt_consumer where time>now()-100m group by “server”
order by “time” asc limit 5

name: mqtt_consumer
tags: server=copper
time callid


1557745074984701258 20
1557745075984896607 20
1557745076986885320 20
1557745077987065206 20
1557745078987666812 20

name: mqtt_consumer
tags: server=dummy
time callid


1557743476450718138 0
1557743487627073587 0
1557743956638171048 0
1557743967739584863 0
1557744437052067885 0

name: mqtt_consumer
tags: server=iodine
time callid


1557746518841453207 5
1557746519834996465 5
1557746520835677473 5
1557746521840783283 5
1557746522836542594 5

So, I get three sets of results, each set grouped according to “server” and
each group sorted in order of “time”.

What are you getting and what are you expecting instead?

Antony.

#5

Ok now is working. Not before, I dont know why. But thanks for your help.

I have another question regarding the auto generated field (from Telegraf) “time”. (Example value by me: 1557518403000000000) Can i change the format? Maybe via Telegraf.conf?

regards

#6

I think i need this format : 2015-09-18T21:36:00Z. I’ll grap this via php. I need a time period.

#7

Like this: SELECT “water_level” FROM “h2o_feet” WHERE time > ‘2015-09-18T21:24:00Z’ AND time < ‘2015-09-20T21:24:00Z’

#8

Hi me again. The time issue is solved. Simply precision rfc3339. That it.
But the group by string field is not working.

Sql statement:

SELECT probe,lastvalue,lastvalue_raw FROM data_traffic WHERE time >= ‘2019-05-13T00:00:00Z’ AND time <= ‘2019-05-14T23:59:00Z’ GROUP BY probe ORDER BY time ASC
or simply

SELECT probe,lastvalue,lastvalue_raw FROM prtg_data_traffic GROUP BY probe ORDER BY time ASC

Database example data:

time probe lastvalue lastvalue_raw


2019-05-13T20:00:03Z Test 1 1.147 GB 1147.2643
2019-05-13T20:00:03Z Test 2 1.158 GB 1158.1432
2019-05-13T20:00:03Z Test 3 1.161 GB 1160.6873
2019-05-14T08:00:03Z Test 1 899 GB 899.2385
2019-05-14T08:00:03Z Test 2 899 GB 898.7219
2019-05-14T08:00:03Z Test 3 919 GB 919.0146
2019-05-14T08:00:03Z Test 4 931 GB 930.6706
2019-05-14T08:00:03Z Test 5 935 GB 934.9691

The output is not in blocks grouped by probe and then ordered by time. I cant use order by time, probe.

regards

#9

I believe you’re getting this data into Influx via telegraf.

Do you have:

tag_keys = [“probe”]

in your /etc/telegraf/telegraf.conf?

Antony.

#10

Hey!

No. Only the identifier/index

tag_keys = [“objid”]

#11

Ok! Now its working. My mistake. Thanks for your help.

group by objid