Select intervals / first and last occurrence of column combinations from PostgreSQL

Question

-- I have PostgreSQL database with table

CREATE TABLE timer
(
  id character varying(12),
  date timestamp without time zone,
  id1 character varying(10),
  id2 character varying(10)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE timer
  OWNER TO postgres;

-- and data

insert into timer values ('001', '2015-01-01 12:00:00', 100, null);
insert into timer values ('002', '2015-01-01 12:00:00', 200, null);
insert into timer values ('003', '2015-01-01 12:00:10', 100, null);
insert into timer values ('004', '2015-01-01 12:00:10', 200, null);
insert into timer values ('005', '2015-01-01 12:00:20', 100, 'aaaa');
insert into timer values ('006', '2015-01-01 12:00:20', 200, null);
insert into timer values ('007', '2015-01-01 12:00:30', 100, 'aaaa');
insert into timer values ('008', '2015-01-01 12:00:30', 200, null);
insert into timer values ('009', '2015-01-01 12:00:40', 100, 'aaaa');
insert into timer values ('010', '2015-01-01 12:00:40', 200, 'bbbb');
insert into timer values ('011', '2015-01-01 12:00:50', 100, 'aaaa');
insert into timer values ('012', '2015-01-01 12:00:50', 200, 'bbbb');
insert into timer values ('013', '2015-01-01 12:01:00', 100, 'aaaa');
insert into timer values ('014', '2015-01-01 12:01:00', 200, 'aaaa');
insert into timer values ('015', '2015-01-01 12:01:10', 100, 'aaaa');
insert into timer values ('016', '2015-01-01 12:01:10', 200, 'aaaa');
insert into timer values ('017', '2015-01-01 12:01:20', 100, null);
insert into timer values ('018', '2015-01-01 12:01:20', 200, 'aaaa');
insert into timer values ('019', '2015-01-01 12:01:30', 100, null);
insert into timer values ('020', '2015-01-01 12:01:30', 200, 'aaaa');
insert into timer values ('021', '2015-01-01 12:01:40', 100, null);
insert into timer values ('022', '2015-01-01 12:01:40', 200, 'aaaa');
insert into timer values ('023', '2015-01-01 12:01:50', 100, 'bbbb');
insert into timer values ('024', '2015-01-01 12:01:50', 200, 'aaaa');
insert into timer values ('025', '2015-01-01 12:02:00', 100, 'bbbb');
insert into timer values ('026', '2015-01-01 12:02:00', 200, 'aaaa');

select * from timer;
id  |         date        |  id1 |   id2  |
----+---------------------+------+--- ----+
001 | 2015-01-01 12:00:00 | 100  |  null  |
002 | 2015-01-01 12:00:00 | 200  |  null  |
003 | 2015-01-01 12:00:10 | 100  |  null  |
004 | 2015-01-01 12:00:10 | 200  |  null  |
005 | 2015-01-01 12:00:20 | 100  |  aaaa  |
006 | 2015-01-01 12:00:20 | 200  |  null  |
007 | 2015-01-01 12:00:30 | 100  |  aaaa  |
008 | 2015-01-01 12:00:30 | 200  |  null  |
009 | 2015-01-01 12:00:40 | 100  |  aaaa  |
010 | 2015-01-01 12:00:40 | 200  |  bbbb  |
011 | 2015-01-01 12:00:50 | 100  |  aaaa  |
012 | 2015-01-01 12:00:50 | 200  |  bbbb  |
013 | 2015-01-01 12:01:00 | 100  |  aaaa  |
014 | 2015-01-01 12:01:00 | 200  |  aaaa  |
015 | 2015-01-01 12:01:10 | 100  |  aaaa  |
016 | 2015-01-01 12:01:10 | 200  |  aaaa  |
017 | 2015-01-01 12:01:20 | 100  |  null  |
018 | 2015-01-01 12:01:20 | 200  |  aaaa  |
019 | 2015-01-01 12:01:30 | 100  |  null  |
020 | 2015-01-01 12:01:30 | 200  |  aaaa  |
021 | 2015-01-01 12:01:40 | 100  |  null  |
022 | 2015-01-01 12:01:40 | 200  |  aaaa  |
023 | 2015-01-01 12:01:50 | 100  |  bbbb  |
024 | 2015-01-01 12:01:50 | 200  |  aaaa  |
025 | 2015-01-01 12:02:00 | 100  |  bbbb  |
026 | 2015-01-01 12:02:00 | 200  |  aaaa  |

every 10 seconds server receives new data with two id-s. id1 and id2 i have to display result in format:

|     date_start      |      date_end       | id1 | id2  |
+---------------------+---------------------+-----+------+
| 2015-01-01 12:00:00 | 2015-01-01 12:00:10 | 100 | null |
| 2015-01-01 12:00:00 | 2015-01-01 12:00:30 | 200 | null |
| 2015-01-01 12:00:20 | 2015-01-01 12:01:10 | 100 | aaaa |
| 2015-01-01 12:00:40 | 2015-01-01 12:00:50 | 200 | bbbb |
| 2015-01-01 12:01:10 | 2015-01-01 12:02:00 | 200 | aaaa |
| 2015-01-01 12:01:20 | 2015-01-01 12:01:40 | 100 | null |
| 2015-01-01 12:01:50 | 2015-01-01 12:02:00 | 100 | bbbb |

result is in format
first occurrence of combinations id1/id2 and last one before id2 is changed.
so for example row with id:001 starts with values date:'2015-01-01 12:00:00' id1:100 id2:null
and first change for this row is in row id:005 where id2 becomes aaaa
is it possible to do this in one query or I should do some calculations periodically.

Answer 1

You may use simple max and min aggregate functions with GROUP BY to receive result you need:

select min(date) date_start, max(date) date_end, id1, id2
from timer
group by id1, id2
order by date_start, id1 nulls first, id2 nulls first;