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?](https://stackoverflow.com/questions/3800551/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, Apr 17 '20 at 06:34
Thanks, I managed somehow create something similar. Instead of selecting every x date-time, i make it to summarize every n time. — HollowLord, Apr 17 '20 at 11:59

Insaf K · Answer 1 · 2020-04-17T09:32:38.413

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.

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). — HollowLord, Apr 17 '20 at 11:56

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

1 Answers1