Querying all measurements at a specific time


I have database with stock prices. I want to query for a list of prices for every symbol at a specific time. ie. What was the last price value 24 hours ago.

I can do it with individual symbols but it doesn’t make sense when I want to populate a table with 200 symbols.
[select from database where time < now() - ‘24h’ AND “symbol”=xxx ORDER BY time DESC LIMIT 1’]

How can I get a single price for every symbol with 1 query?


I can think of two ways to do this, the first is using the symbols as a tag:

stocks,ticker=APPL price=50
stocks,ticker=GOOG price=42

Then you could have a query like select last(price) from stocks where time < now() group by ticker.

The other method would be to store as fields like:

stocks appl=50,goog=42

Then you can use a query like your example:

select * from stocks where time < now() - '24h' order by time desc limit 1

If you go the second route you might need to aggregate over a short period in case not all samples are at the same time.