'How to get all the records from a table with 1 minute or 1 hourly interval in postgreSQL

How can i implement a query in postgreSQL to fetch the data with all columns at every 1 minute or 1 hourly internal.Attached screenshot of the data. If we take 1 minute internal example then i will need 37th record, 50th record etc etc,

Sample data

sqlpostgresql


Solution 1:[1]

Fetching data every minute from my_table based on the column time :

    SELECT DISTINCT ON (date_trunc('minute', "time" :: timestamp )) *
      FROM my_table
     ORDER BY date_trunc('minute', "time" :: timestamp ), "time"

Fetching data every 30 seconds from my_table based on the column time :

SELECT DISTINCT ON (d.date_interval) *
  FROM my_table AS t
 INNER JOIN 
     ( SELECT generate_series(date_trunc('minute', min(time)), max(time), interval '30 seconds') AS date_interval
         FROM my_table
     ) AS d
    ON d.date_interval <= t.time
   AND d.date_interval + interval '30 seconds' > t.time
 GROUP BY d.date_interval
 ORDER BY d.date_interval, t.time

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1

Related Questions