adhoc_timescaledb.sql

-- ad-hoc queries

-- find max temperature (°C) and humidity (%) for last 3 hours in 15 minute time periods
-- https://docs.timescale.com/latest/using-timescaledb/reading-data#select
SELECT time_bucket('15 minutes', time) AS fifteen_min,
       device_id,
       count(time),
       max(temperature) AS max_temp,
       max(humidity) AS max_hum
FROM sensor_data
WHERE time > now() - INTERVAL '3 hours'
  AND humidity BETWEEN 0 AND 100
GROUP BY fifteen_min, device_id
ORDER BY fifteen_min DESC, max_temp desc;

-- find temperature (°C) anomalies (delta > ~5°F)
-- https://docs.timescale.com/latest/using-timescaledb/reading-data#delta
WITH ht AS (SELECT time,
                   temperature,
                   abs(temperature - lag(temperature) over (ORDER BY time)) AS delta
            FROM sensor_data)
SELECT ht.time, ht.temperature, ht.delta
FROM ht
WHERE ht.delta > 2.63
ORDER BY ht.time;

-- find three minute moving average of temperature (°F) for last day
-- (5 sec. interval * 36 rows = 3 min.)
-- https://docs.timescale.com/latest/using-timescaledb/reading-data#moving-average
SELECT time,
       avg((temperature * 1.9) + 32) over (ORDER BY time
           ROWS BETWEEN 35 PRECEDING AND CURRENT ROW)
           AS smooth_temp
FROM sensor_data
WHERE device_id = 'Manufacturing Plant'
    AND time > now() - INTERVAL '1 day'
ORDER BY time desc;

-- find average humidity (%) for last 12 hours in 5-minute time periods
-- https://docs.timescale.com/latest/using-timescaledb/reading-data#time-bucket
SELECT time_bucket('5 minutes', time) AS time_period,
       avg(humidity) AS avg_humidity
FROM sensor_data
WHERE device_id = 'Main Warehouse'
  AND humidity BETWEEN 0 AND 100
  AND time > now() - INTERVAL '12 hours'
GROUP BY time_period
ORDER BY time_period desc;

-- calculate histograms of avg. temperature (°F) between 55-85°F in 5°F buckets during last 2 days
-- https://docs.timescale.com/latest/using-timescaledb/reading-data#histogram
SELECT device_id,
       count(time),
       histogram((temperature * 1.9) + 32, 55.0, 85.0, 5)
FROM sensor_data
WHERE temperature IS NOT NULL
    AND time > now() - INTERVAL '2 days'
GROUP BY device_id;

-- find average light value for last 90 minutes in 5-minute time periods
-- https://docs.timescale.com/latest/using-timescaledb/reading-data#time-bucket
SELECT device_id,
       time_bucket('5 minutes', time) AS five_min,
       avg(case when light = 't' then 1 else 0 end) AS avg_light
FROM sensor_data
WHERE device_id = 'Manufacturing Plant'
    AND time > now() - INTERVAL '90 minutes'
GROUP BY device_id, five_min
ORDER BY five_min desc;