Data At every nth Interval Using Postgres

Question

I am filtering my data between start date and end End date. What I am trying to do is I want to filter data between '31-12-2015 11:19:00' And '31-12-2015 11:25:00' with '2' minutes interval

CREATE TABLE table_1
(
  timestamp_col timestamp without time zone,
  value_col integer
);

INSERT INTO table_1(
        timestamp_col, value_col)
VALUES ('2015-12-31 11:19:10', 10),('2015-12-31 11:20:10', 23),
('2015-12-31 11:21:10', 32),('2015-12-31 11:22:10', 43),
('2015-12-31 11:23:10', 55),('2015-12-31 11:24:10', 32),('2015-12-31 11:25:10', 100)
;

This is the Query I am using but not getting result as required

SELECT timestamp_col, value_col
  FROM table_1
  Where timestamp_col Between '31-12-2015 11:00:00' AND '31-12-2015 12:00:00'
  AND date_part('minutes',timestamp_col)::int % 2 = 0

It is resulting data from "2015-12-31 11:20:10" but i want it to start from "2015-12-31 11:19:10" , "2015-12-31 11:21:10" ...... <2 minutes Interval from starting timestamp between the start date and end date>

Gordon Linoff · Accepted Answer · 2015-12-31 12:45:37Z

1

This is too long for a comment.

First, use ISO standard date formats: YYYY-MM-DD:

SELECT timestamp_col, value_col
FROM table_1
WHERE timestamp_col Between '2015-12-31 11:00:00' AND '2015-12-31 12:00:00' AND
      date_part('minutes', timestamp_col)::int % 2 = 0;
------^

This doesn't affect your results. But the second condition is limiting the results to even-numbered minutes. Hence, you cannot get 11:19, because "19" is odd.

If you want the parity based on the first timestamp, you can calculate the minimum timestamp and compare that:

SELECT timestamp_col, value_col
FROM (SELECT t1.*, MIN(timestamp_col) OVER () as mintc
      FROM table_1 t1
     ) t1
Where timestamp_col Between '2015-12-31 11:00:00' AND '2015-12-31 12:00:00' AND
      date_part('minutes', timestamp_col)::int % 2 = date_part('minutes', mintc)::int % 2;

answered Dec 31, 2015 at 12:45

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Parth Desai Over a year ago

No not getting results as required

Collectives™ on Stack Overflow

Data At every nth Interval Using Postgres

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related