CODEWITHSUNDEEP
sqldatabasepostgresql
Luis Henrique
Luis Henrique

Reputation: 771

How to group and select data based on a date and time column over a 10-minute period?

How to group and select data based on a date and time column over a 10-minute period?

Select:

SELECT *
FROM tbl
WHERE datecollect IN (
SELECT generate_series(
        (SELECT min(date_trunc('minute', datecollect)) FROM tbl), 
        (SELECT max(date_trunc('minute', datecollect)) FROM tbl), 
        '10 minute'::interval));

Table:

CREATE TABLE tbl (
  valuex varchar,
  datecollect timestamp);

INSERT INTO tbl (valuex,datecollect) VALUES ('teste','2020-02-25 01:00:41');
INSERT INTO tbl (valuex,datecollect) VALUES ('teste','2020-02-25 01:05:36');
INSERT INTO tbl (valuex,datecollect) VALUES ('teste','2020-02-25 01:10:41');
INSERT INTO tbl (valuex,datecollect) VALUES ('teste','2020-02-25 01:15:11');
INSERT INTO tbl (valuex,datecollect) VALUES ('teste','2020-02-25 03:00:11');
INSERT INTO tbl (valuex,datecollect) VALUES ('teste','2020-02-25 03:05:41');
INSERT INTO tbl (valuex,datecollect) VALUES ('teste','2020-02-25 03:10:11');
INSERT INTO tbl (valuex,datecollect) VALUES ('teste','2020-02-25 03:15:11');

SQL VIEW

Output:

0 records

Upvotes: 0

Views: 41

Answers (1)

Nick
Nick

Reputation: 147236

You need to JOIN your generated series to tbl where datecollect is in the 10 minute interval starting at the time in the series. This will give you all rows for a given interval; you can then aggregate as required, grouping by pstart. If you want to get 0 values where there are no matching values for an interval, use a LEFT JOIN instead.

SELECT *
FROM (
  SELECT generate_series(
            MIN(date_trunc('minute', datecollect)), 
            MAX(date_trunc('minute', datecollect)), 
            '10 minute'::interval
         ) AS pstart
  FROM tbl
) dt
JOIN tbl ON tbl.datecollect > dt.pstart AND tbl.datecollect < dt.pstart + interval '10 minute'

Output (for your sample data):

pstart                  valuex  datecollect
2020-02-25T01:00:00Z    teste   2020-02-25T01:00:41Z
2020-02-25T01:00:00Z    teste   2020-02-25T01:05:36Z
2020-02-25T01:10:00Z    teste   2020-02-25T01:10:41Z
2020-02-25T01:10:00Z    teste   2020-02-25T01:15:11Z
2020-02-25T03:00:00Z    teste   2020-02-25T03:00:11Z
2020-02-25T03:00:00Z    teste   2020-02-25T03:05:41Z
2020-02-25T03:10:00Z    teste   2020-02-25T03:10:11Z
2020-02-25T03:10:00Z    teste   2020-02-25T03:15:11Z

Demo on SQLFiddle

Upvotes: 1

Related Questions