CODEWITHSUNDEEP
sqlpostgresql
NolanPower
NolanPower

Reputation: 409

PSQL Select 1 record every 10 minutes

I have a database where I have data inserted every minute, and I'm looking to pull out the record for every 10 minute interval:

Example: I have data from 9:00 to 5:00 with 1 record every minute, I want to pull out 9:10, 9:20, 9:30, 9:40, etc.

Right now I have:

select * from main where date = '2016-06-02' and time > '09:00:00' and time < '17:00:00'

This pulls all the records, but I want to limit it to only 10 minute intervals.

Upvotes: 1

Views: 2653

Answers (3)

Benjamin Crouzier
Benjamin Crouzier

Reputation: 41875

I solved it with the query below. My date column is called ts. What this query does is to first group every row in 5min buckets, order them by ts, and grab the first one. For 10mins, replace 300 with 600.

select distinct on (CAST (extract(epoch from date_trunc('second', ts)) AS integer) / 300) ts
   ts, CAST (extract(epoch from date_trunc('second', ts)) AS integer) % 300 as sec,
   col_a, col_b


from main
where ts > '2020-02-25'

order by CAST (extract(epoch from date_trunc('second', ts)) AS integer) / 300 asc, ts asc

limit 500;

Upvotes: 1

David Aldridge
David Aldridge

Reputation: 52346

Another method, just for fun ...

select *
from   main
where date in (
        select generate_series(timestamp '2016-06-02 09:00:00', 
                               timestamp '2016-06-02 16:50:00',
                               '10 minute'::interval));

Upvotes: 1

NolanPower
NolanPower

Reputation: 409

I ended up figuring it out at least somewhat

select * from main where date = '2016-06-02' and and time > '09:00:00' and time < '17:00:00' and  time::varchar like '%:%0:%';

Upvotes: 2

Related Questions