title: How do I select every nth minute row in SQL (postgresql)
body: <p>Currently I'm working on creating a dashboard for web connected sensor. I'm using Blynk + Metabase + PostgreSQL setup. </p>
  
  <p>My current SQL command to display a graph in Metabase is :</p>
  
  <pre><code>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
  </code></pre>
  
  <p>The output of SQL Query :</p>
  
  <pre><code>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
  ...
  </code></pre>
  
  <p>However, what i'm aiming for is more like this</p>
  
  <pre><code>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
  ...
  </code></pre>
  
  <p>How can I select every nth minute row of every pin?</p>
  
  <p>EDIT 17/04/2020 - Current Solution</p>
  
  <pre><code>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
  </code></pre>