Time-Series Analytics

Tutorial 3 of 5

This tutorial models sensor readings with timestamps and intervals. You will use DATE_TRUNC, EXTRACT, AGE, TO_CHAR, recursive CTEs to fill date gaps, and window frames for moving averages—showcasing the temporal SQL that mskql supports natively.

1. Schema and seed data

CREATE TABLE sensors (
    id        SERIAL PRIMARY KEY,
    name      TEXT NOT NULL,
    location  TEXT NOT NULL
);

CREATE TABLE readings (
    id         SERIAL PRIMARY KEY,
    sensor_id  INT NOT NULL REFERENCES sensors(id),
    recorded   TIMESTAMP NOT NULL,
    temp_c     INT NOT NULL,
    humidity   INT
);

INSERT INTO sensors (name, location) VALUES
    ('TH-01', 'Warehouse A'),
    ('TH-02', 'Warehouse B'),
    ('TH-03', 'Office');

INSERT INTO readings (sensor_id, recorded, temp_c, humidity) VALUES
    (1, '2025-01-10 08:00:00', 18, 45),
    (1, '2025-01-10 12:00:00', 22, 40),
    (1, '2025-01-10 18:00:00', 20, 42),
    (1, '2025-01-11 08:00:00', 17, 48),
    (1, '2025-01-11 12:00:00', 23, 38),
    (1, '2025-01-11 18:00:00', 21, 41),
    (1, '2025-01-12 08:00:00', 19, 44),
    (1, '2025-01-12 12:00:00', 24, 36),
    (2, '2025-01-10 08:00:00', 15, 55),
    (2, '2025-01-10 12:00:00', 19, 50),
    (2, '2025-01-10 18:00:00', 16, 53),
    (2, '2025-01-11 08:00:00', 14, 58),
    (2, '2025-01-11 12:00:00', 20, 48),
    (3, '2025-01-10 09:00:00', 21, 35),
    (3, '2025-01-10 14:00:00', 23, 33),
    (3, '2025-01-11 09:00:00', 22, 34),
    (3, '2025-01-11 14:00:00', 24, 32);

2. Daily averages with DATE_TRUNC

Truncate timestamps to the day and compute daily averages per sensor.

SELECT s.name,
       DATE_TRUNC('day', r.recorded)
         AS day,
       AVG(r.temp_c) AS avg_temp,
       AVG(r.humidity) AS avg_hum
FROM readings r
JOIN sensors s
  ON r.sensor_id = s.id
WHERE s.name = 'TH-01'
GROUP BY s.name,
  DATE_TRUNC('day', r.recorded)
ORDER BY day;

3. Hourly breakdown with EXTRACT

Pull the hour from each reading to see temperature patterns across the day.

SELECT
  EXTRACT(HOUR FROM r.recorded)
    AS hour,
  AVG(r.temp_c) AS avg_temp,
  COUNT(*) AS readings
FROM readings r
JOIN sensors s
  ON r.sensor_id = s.id
WHERE s.location = 'Warehouse A'
GROUP BY
  EXTRACT(HOUR FROM r.recorded)
ORDER BY hour;

4. Time since last reading with AGE

Use AGE to compute the interval between the latest reading per sensor and a reference timestamp.

SELECT s.name,
       MAX(r.recorded) AS last_read,
       AGE('2025-01-13 00:00:00',
           MAX(r.recorded))
         AS time_since
FROM readings r
JOIN sensors s
  ON r.sensor_id = s.id
GROUP BY s.name
ORDER BY s.name;

5. Formatted output with TO_CHAR

Format timestamps for human-readable display.

SELECT s.name,
       TO_CHAR(r.recorded,
         'YYYY-MM-DD HH24:MI')
         AS time,
       r.temp_c
FROM readings r
JOIN sensors s
  ON r.sensor_id = s.id
WHERE s.name = 'TH-03'
ORDER BY r.recorded;

6. Moving average with window frames

Compute a 3-reading moving average of temperature for sensor TH-01, using a window frame.

SELECT r.recorded,
       r.temp_c,
       AVG(r.temp_c) OVER (
         ORDER BY r.recorded
         ROWS BETWEEN 2 PRECEDING
           AND CURRENT ROW
       ) AS moving_avg
FROM readings r
WHERE r.sensor_id = 1
ORDER BY r.recorded;

7. Fill date gaps with a recursive CTE

Generate a continuous date series and LEFT JOIN readings to it, exposing days with no data.

WITH RECURSIVE dates AS (
  SELECT '2025-01-10'::DATE AS dt
  UNION ALL
  SELECT (dt + 1)::DATE
  FROM dates
  WHERE dt < '2025-01-14'::DATE
)
SELECT d.dt AS day,
  COALESCE(
    (SELECT AVG(r.temp_c)
     FROM readings r
     WHERE r.sensor_id = 2
       AND DATE_TRUNC('day',
           r.recorded) = d.dt
           ::TIMESTAMP),
    0) AS avg_temp
FROM dates d
ORDER BY d.dt;

Days with no readings for sensor TH-02 show 0 thanks to COALESCE. In a real dashboard you might display these as “no data” or interpolate.

What you learned

• DATE_TRUNC to bucket timestamps by day, hour, or month
• EXTRACT to pull individual fields (hour, day, month) from timestamps
• AGE to compute intervals between timestamps
• TO_CHAR for formatted timestamp display
• :: cast operator for type conversions (e.g. '2025-01-10'::DATE)
• Recursive CTEs to generate date series and fill gaps
• Window frames (ROWS BETWEEN n PRECEDING AND CURRENT ROW) for moving averages