CODEWITHSUNDEEP
postgresql
RonEskinder
RonEskinder

Reputation: 527

Get values repeated over 2 minute period in Postgres

I have an events table, i need to get whenever an overspeed alarm has gone for over 2 minutes for any deviceid and flag them with "emailSent"=1.

What i'm doing is, grab all the events per vehicle like so SELECT * FROM tc_events tm where "emailSent" = 0 and date(servertime) = date(now()); and then 1 by 1 comparing to its last known value like so

SELECT * 
FROM tc_events tm 
where deviceid = ? 
and id not in (?) 
and "attributes" = '{"alarm":"overspeed"}' 
and servertime > NOW() - INTERVAL '2 minute' 
and date(servertime) = date(now())
limit 1;

Maybe you guys can help me to get it in one single query.

CREATE TABLE public.tc_events (
    id serial NOT NULL,
    "type" varchar(128) NOT NULL,
    servertime timestamp NOT NULL,
    deviceid int4 NULL,
    positionid int4 NULL,
    geofenceid int4 NULL,
    "attributes" varchar(4000) NULL,
    maintenanceid int4 NULL,
    "emailSent" int4 NULL DEFAULT 0,
    CONSTRAINT tc_events_pkey PRIMARY KEY (id)
);

id    |type |servertime         |deviceid|positionid|geofenceid|attributes           |maintenanceid|emailSent|
------|-----|-------------------|--------|----------|----------|---------------------|-------------|---------|
631014|alarm|2020-09-03 20:46:36|     962|   8162779|          |{"alarm":"overspeed"}|             |        0|
630980|alarm|2020-09-03 20:42:43|     962|   8162521|          |{"alarm":"overspeed"}|             |        0|
630971|alarm|2020-09-03 20:40:08|     962|   8162385|          |{"alarm":"overspeed"}|             |        0|
607651|alarm|2020-09-02 14:46:13|     557|   7871167|          |{"alarm":"overspeed"}|             |        1|
607616|alarm|2020-09-02 14:44:33|     557|   7870620|          |{"alarm":"overspeed"}|             |        1|
591124|alarm|2020-09-01 17:13:05|     239|   7652421|          |{"alarm":"overspeed"}|             |        0|
590225|alarm|2020-09-01 16:30:31|     148|   7642031|          |{"alarm":"overspeed"}|             |        0|
590172|alarm|2020-09-01 16:28:35|     148|   7641467|          |{"alarm":"overspeed"}|             |        0|
588176|alarm|2020-09-01 15:14:42|     148|   7619691|          |{"alarm":"overspeed"}|             |        0|
582210|alarm|2020-09-01 11:34:11|     725|   7543204|          |{"alarm":"overspeed"}|             |        0|

Upvotes: 0

Views: 71

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656666

Assuming that the mere existence of another overspeed alarm within the past two minutes constitutes "ongoing", EXISTS can do the job:

SELECT * 
FROM   tc_events tm
WHERE  deviceid = ? 
AND    id not in (?) 
AND    attributes = '{"alarm":"overspeed"}' 
AND    servertime > NOW() - INTERVAL '2 minute' -- only look at very recent rows?
AND    date(servertime) = date(now())  -- why? local midnight cancels "ongoing"?
AND    EXISTS (
   SELECT FROM tc_events
   WHERE  deviceid = tm.deviceid
   -- AND    id not in (?)  -- exclude here, too?
   AND    id <> tm.id  -- exclude self
   AND    attributes = '{"alarm":"overspeed"}' 
   AND    servertime <= tm.servertime -- same time possible?
   AND    servertime >= tm.servertime - interval '2 minute' 
   );

This reports "ongoing" events repeatedly for every new row.

Aside, your attributes column looks like a JSON document, and varchar(4000) is probably not the best way to store it ...

Upvotes: 1

Related Questions