Select one record per minute group in postgres-CodePudding

I have a big table in a postgres db with location of units. Now I need to retrieve a location for every 60 seconds.

In Mysql, this is a piece of cake: select * from location_table where unit_id = '123' GROUP BY round(timestamp / 60)

But in postgres this seems to be a very hard problem. I also have the timestamps in dateformat rather than in epoch format.

Here is an example of how the table looks

CREATE TABLE location_table (
    unit_id int,
    "timestamp" timestamp(3) without time zone NOT NULL,
    lat double precision,
    lng double precision
);

CodePudding user response：

Use date_trunc() to make sets per minute:

SELECT  * -- most likely not what you want
FROM    location_table 
WHERE   unit_id = 123 -- numbers don't need quotes ' 
GROUP BY date_trunc('minute', 'timestamp');

The * is of course wrong, but I don't know what you want to know about the GROUP so I can't come up with something better.

Edit: When you need a random result from your table, DISTINCT ON () could do the job:

SELECT  DISTINCT ON(date_trunc('minute', timestamp))
     * -- your columns
FROM    location_table;

There are other (standard SQL) solutions as well, like using row_number().