Query all measurements that came into database within a time frame

Telegraf
1

Hello everyone,

I have an influxdb database that is set up this way:

Databases:
-NASDAQ
Meausurements:

  • stock 1
    Fields
    value 1
    value 2
    value 3

  • stock 2
    Fields
    value 1
    value 2
    value 3

  • stock 3
    Fields
    value 1
    value 2
    value 3

From the NASDAQ database, I want to query all of the stock values that came in the past hour (not all of them come in continuously but this is out of scope).

My first step is to first query all the stocks that are inside the database (by using InfluxDBClient in Python):

response = client.query(“SHOW MEASUREMENTS ON NASDAQ”)

I then make single queries for each value of my response (stocks) and add time intervals:

query1 = (‘SELECT * FROM “NASDAQ”.“autogen”.“Stock1” WHERE time > now()-1h’)
query2 = (‘SELECT * FROM “NASDAQ”.“autogen”.“Stock2” WHERE time > now()-1h’)
query3 = (‘SELECT * FROM “NASDAQ”.“autogen”.“Stock3” WHERE time > now()-1h’)
…

This is very inefficient because I have 8,000 stocks within the database and just the “SHOW MEASUREMENTS ON NASDAQ” takes a bit to complete. Moreover, I make 8,000 different queries after.

My ideal solution would be something like Kibana, where you give the time range (in this case “now()-1h”) and it returns all of the measurements that came in within that time range. Is there a way to do something similar in influxdb?

2

Hi ,
this should do it …

SELECT * FROM /(?i)^Stock\./ WHERE time > now() - 1h;
3

Hi Mark,
Thanks for your quick reply.

Thanks to you, I found this amazing page that I did not know existed: https://influxdbcom.readthedocs.io/en/latest/content/docs/v0.9/query_language/data_exploration/[https://influxdbcom.readthedocs.io/en/latest/content/docs/v0.9/query_language/data_exploration/]

I tried your solution and initially it would give me an error (“Database Name Required”). The reason was that
with the python client you NEED to specify the database in the query itself. Took me a bit to figure it out.

Anyways, this is what I came up with that works perfectly for me:

SELECT * FROM "NASDAQ"."autogen"./.*/ WHERE time > now() - 24

I am still looking for a solution to query from multiple databases but this is okay for now.

1 Like