-- Creating a table with a TIMESTAMP column
CREATE TABLE temperature_readings (
    id SERIAL PRIMARY KEY,
    reading_time TIMESTAMP NOT NULL,
    value DECIMAL NOT NULL
);

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.

-- Creating an index on the reading_time column
CREATE INDEX idx_reading_time ON temperature_readings (reading_time);

• 1.
• 2.

-- Selecting records from the last 24 hours
SELECT * FROM temperature_readings
WHERE reading_time > NOW() - INTERVAL '1 day';

-- Selecting records between two timestamps
SELECT * FROM temperature_readings
WHERE reading_time BETWEEN '2023-01-01 00:00:00' AND '2023-01-02 00:00:00';

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.

-- Averaging values by hour
SELECT date_trunc('hour', reading_time) AS hour, AVG(value)
FROM temperature_readings
GROUP BY hour
ORDER BY hour;

• 1.
• 2.
• 3.
• 4.
• 5.

-- Creating a materialized view for daily averages
CREATE MATERIALIZED VIEW daily_avg AS
SELECT date_trunc('day', reading_time) AS day, AVG(value)
FROM temperature_readings
GROUP BY day;

• 1.
• 2.
• 3.
• 4.
• 5.