SELECT data

You can query data from a hypertable using a standard SELECT command. All SQL clauses and features are supported.

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:

  1. SELECT * FROM conditions ORDER BY time DESC LIMIT 100;

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

  1. SELECT COUNT(*) FROM conditions
  2. 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 examples uses the time_bucket function to aggregate data into 15-minute buckets:

  1. SELECT time_bucket('15 minutes', time) AS fifteen_min,
  2. location,
  3. COUNT(*),
  4. MAX(temperature) AS max_temp,
  5. MAX(humidity) AS max_hum
  6. FROM conditions
  7. WHERE time > NOW() - INTERVAL '3 hours'
  8. GROUP BY fifteen_min, location
  9. 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:

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