CODEWITHSUNDEEP
sqlpostgresql
Parth Desai
Parth Desai

Reputation: 209

Data At every nth Interval Using Postgres

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>

Upvotes: 0

Views: 198

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270463

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;

Upvotes: 1

Related Questions