Data can be queried from a hypertable using the standard
SELECT SQL command
(PostgreSQL docs), including with arbitrary
GROUP BY and
ORDER BY commands, joins, subqueries, window functions,
user-defined functions (UDFs),
HAVING clauses, and so on.
From basic queries:
-- Return the most recent 100 entries in the table 'conditions' ordered newest to oldest SELECT * FROM conditions ORDER BY time DESC LIMIT 100; -- Return the number of data entries written in past 12 hours SELECT COUNT(*) FROM conditions WHERE time > NOW() - INTERVAL '12 hours';
To more advanced SQL queries:
-- Information about each 15-min period for each location -- over the past 3 hours, ordered by time and temperature 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; -- How many distinct locations with air conditioning -- have reported data in the past day SELECT COUNT(DISTINCT location) FROM conditions JOIN locations ON conditions.location = locations.location WHERE locations.air_conditioning = True AND time > NOW() - INTERVAL '1 day'