SELECT data

SELECT against a hypertable works like any other table: filters, joins, aggregates, and window functions all apply. The examples below cover common time-window patterns.

Try this with your AI agent

In Tiger Cloud, set up Tiger MCP and ask your AI agent to do this for you. See example prompts to get started, and best practices to keep it safe.

Basic query examples

Here are some examples of basic SELECT queries.

Return the 100 most-recent entries in the table conditions. Order the rows from newest to oldest:

SELECT * FROM conditions ORDER BY time DESC LIMIT 100;

Return the number of entries written to the table conditions in the last 12 hours:

SELECT COUNT(*) FROM conditions
  WHERE time > NOW() - INTERVAL '12 hours';

Advanced query examples

Here are some examples of more advanced SELECT queries.

Get information about the weather conditions at each location, for each 15-minute period within the last 3 hours. Calculate the number of measurements taken, the maximum temperature, and the maximum humidity. Order the results by maximum temperature.

This example uses the time_bucket function to aggregate data into 15-minute buckets:

SELECT time_bucket('15 minutes', time) AS fifteen_min,
    location,
    COUNT(*),
    MAX(temperature) AS max_temp,
    MAX(humidity) AS max_hum
  FROM conditions
  WHERE time > NOW() - INTERVAL '3 hours'
  GROUP BY fifteen_min, location
  ORDER BY fifteen_min DESC, max_temp DESC;

Count the number of distinct locations with air conditioning that have reported data in the last day:

SELECT COUNT(DISTINCT conditions.location) FROM conditions
  JOIN locations
    ON conditions.location = locations.location
  WHERE locations.air_conditioning = True
    AND time > NOW() - INTERVAL '1 day';