How do I select every nth minute row in SQL (postgresql)

Question

Currently I'm working on creating a dashboard for web connected sensor. I'm using Blynk + Metabase + PostgreSQL setup.

My current SQL command to display a graph in Metabase is :

SELECT ts, extract(hour from ts) as h, extract(minute from ts) as m, extract(second from ts) as s, device_id , pin, doublevalue
FROM "public"."reporting_raw_data"
WHERE device_id = 81224 AND (extract(hour from ts) = 8 OR extract(hour from ts) = 13) AND (extract(minute from ts) = 0) AND (pin BETWEEN 10 AND 13)
ORDER BY ts ASC
LIMIT 43200

The output of SQL Query :

ts                          |   h|  m|  s       |device_id  |pin|double_value 
=============================================================================
March 23, 2020, 08:00 AM    |   8|  0|  42.21   |81,224     |12 |0
March 23, 2020, 08:00 AM    |   8|  0|  42.33   |81,224     |11 |0
March 23, 2020, 08:00 AM    |   8|  0|  42.35   |81,224     |10 |21.09
March 23, 2020, 08:00 AM    |   8|  0|  44.24   |81,224     |12 |0
March 23, 2020, 08:00 AM    |   8|  0|  44.36   |81,224     |11 |0
March 23, 2020, 13:00 AM    |  13|  0|  45.21   |81,224     |12 |0
March 23, 2020, 13:00 AM    |  13|  0|  48.33   |81,224     |11 |0
March 23, 2020, 13:00 AM    |  13|  0|  33.35   |81,224     |10 |19.31
...

However, what i'm aiming for is more like this

ts                          |   h|  m|  s       |device_id  |pin|double_value
=============================================================================
March 23, 2020, 08:00 AM    |   8|  0|  42.21   |81,224     |12 |0
March 23, 2020, 08:00 AM    |   8|  0|  42.33   |81,224     |11 |0
March 23, 2020, 08:00 AM    |   8|  0|  42.35   |81,224     |10 |21.09
March 23, 2020, 13:00 AM    |  13|  0|  45.21   |81,224     |12 |0
March 23, 2020, 13:00 AM    |  13|  0|  48.33   |81,224     |11 |0
March 23, 2020, 13:00 AM    |  13|  0|  33.35   |81,224     |10 |19.31
...

How can I select every nth minute row of every pin?

EDIT 17/04/2020 - Current Solution

SELECT
    MAX(ts) as t,
    pin,
    device_id,
    date(ts) as d,
    extract(hour from ts) as h,
    extract(minute from ts) as m,
    AVG(doublevalue) as value


FROM "public"."reporting_raw_data"
WHERE device_id = 81224 
    AND ( extract(hour from ts) IN (8, 12, 16, 20)) 
    AND extract(minute from ts) = 0
GROUP BY 
    device_id,
    d,
    h,
    m,
    pin
LIMIT 1000

Does this help? Select first row in each GROUP BY group? Seems to me that you want to group by pin and h and m but only retrieve the first row in each "group". — Abra
– Abra, Commented Apr 17, 2020 at 6:34
Thanks, I managed somehow create something similar. Instead of selecting every x date-time, i make it to summarize every n time. — HollowLord
– HollowLord, Commented Apr 17, 2020 at 11:59

Insaf K · Accepted Answer · 2020-04-17 09:32:38Z

1

You could use DISTINCT ON(), a PostgreSQL SQL extension(That means, it is not part of standard SQL).

The DISTINCT ON expression(s) must match the leftmost ORDER BY expression(s)

So your query with DISTINCT ON would look like this:

SELECT DISTINCT ON (ts, pin) ts, extract(hour from ts) as h, extract(minute from ts) as m, extract(second from ts) as s, device_id , pin, doublevalue
FROM "public"."reporting_raw_data"
WHERE device_id = 81224 AND (extract(hour from ts) = 8 OR extract(hour from ts) = 13) AND (extract(minute from ts) = 0) AND (pin BETWEEN 10 AND 13)
ORDER BY ts, pin ASC
LIMIT 43200

(I've added DISTINCT ON in SELECT clause and added one more column in ORDER BY)

What DISTINCT ON(ts, pin) means, keep only those rows which have unique values for (ts, pin) pair.

For more info on DISTINCT ON, please refer PostgreSQL docs.

edited Apr 17, 2020 at 9:32

answered Apr 17, 2020 at 9:27

Insaf K

3664 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

HollowLord Over a year ago

I tried it and DISTINCT ON won't work in timestamp (ts) since my device send more than 1 data in a minute. Thus every row ts value is different (aka distinct).

Collectives™ on Stack Overflow

How do I select every nth minute row in SQL (postgresql)

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related