CODEWITHSUNDEEP
sqldatabasepostgresql
Kijoo
Kijoo

Reputation: 13

Count rows by time interval of 10 minutes postgresql

i've been trying to find a solution for this query but i'm hitting the wall from the begining i'm trying to count the number of bought and sold items per 10 mins intervals

date_action ACTION 2018-03-16 00:00:00 bought 2018-03-16 00:03:00 sold 2018-03-16 00:04:00 bought
2018-03-16 00:27:00 sold
2018-03-16 00:29:00 sold

the output that i'm trying to get should be something like that

time_interval ACTION count 2018-03-16 00:00:00 bought 2 2018-03-16 00:00:00 sold 1 2018-03-16 00:20:00 bought 1 2018-03-16 00:20:00 sold 2 i'm new to stack overflow so i hope that my uestion is clear

Upvotes: 1

Views: 1097

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

Truncating to 10 minute intervals takes a bit of work, but here is one method:

select (date_trunc(date_action, 'hour') +
        (date_part('minute', date_action) / 6) * interval '10 minute'
       ) as time_interval,
       action, count(*)
from t
group by time_interval, action
order by time_interval, action;

Upvotes: 1

Related Questions