Hi all,
For each event of each type in each interval I want to compute the sum
of events referencing the same relayNet (via host) and insert/update 1
row in SumOfEvents.
All intervals start at a time, minimum 5 minutes in the past, rounded
down modulo 5 minutes like:
SELECT DATE_TRUNC('MINUTES', NOW () - ('0:' || (SELECT
(EXTRACT('MINUTE' FROM NOW())::INT % 5) + 5 ) || ':0')::INTERVAL);
CREATE TABLE host ( id SERIAL PRIMARY KEY, relayNetFK INT REFERENCES relayNet
ON DELETE CASCADE
)
CREATE TABLE event ( id SERIAL PRIMARY KEY, type CHAR NOT NULL CHECK (
typeIN ('C', 'A', 'D', 'S', 'R') ), timeOfEvent timestamp NOT NULL DEFAULT NOW(),
hostfk INT NOT NULL REFERENCES host ON DELETE CASCADE
)
CREATE TABLE relayNet ( id SERIAL PRIMARY KEY, name TEXT NOT NULL UNIQUE
)
CREATE TYPE eventIntervals AS ENUM ('5m', '30m', '3h', '24h', '30d');
CREATE TABLE SumOfEvents ( id SERIAL PRIMARY KEY, type CHAR NOT NULL
CHECK( type IN ('C', 'A', 'D', 'S', 'R') ), startTime timestamp NOT NULL, interval eventIntervals NOT
NULL, value INT, -- sum relayNetFK INT REFERENCES relayNet ON
DELETECASCADE,
UNIQUE (type, interval, relayNetFK)
)
Can this be done w/o procedural code?
Any hints?
Thanks, Axel
---
axel.rau@chaos1.de PGP-Key:29E99DD6 +49 151 2300 9283 computing @
chaos claudius