-- 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.
You may use simple
max
andmin
aggregate functions withGROUP BY
to receive result you need: