I'm using PostgreSQL 9.4.
Given a table with a timestamp and a boolean flag of read status:
CREATE TABLE status (
id serial NOT NULL,
created timestamp with time zone NOT NULL,
read boolean NOT NULL,
CONSTRAINT status_pkey PRIMARY KEY (id),
CONSTRAINT status_unique_created_read UNIQUE(created, read)
);
I want to retrieve the greatest interval of consecutive records that share the same status. Each change of the read flag should start another group of records.
The final goal is to get the greatest interval of created dates that share the read status True.
As an example, let's populate the table with this records:
INSERT INTO status (created, read) VALUES ('2016-02-23 14:53:39.668225-03', true);
INSERT INTO status (created, read) VALUES ('2016-02-24 09:00:07.384002-03', true);
INSERT INTO status (created, read) VALUES ('2016-02-26 19:33:00.677397-03', true);
INSERT INTO status (created, read) VALUES ('2016-02-26 19:58:07.070881-03', true);
INSERT INTO status (created, read) VALUES ('2016-02-28 09:00:47.831193-03', true);
INSERT INTO status (created, read) VALUES ('2016-02-28 09:00:50.632217-03', true);
INSERT INTO status (created, read) VALUES ('2016-02-28 09:00:50.655375-03', true);
INSERT INTO status (created, read) VALUES ('2016-02-28 09:00:54.146508-03', true);
INSERT INTO status (created, read) VALUES ('2016-02-28 09:00:56.524389-03', true);
INSERT INTO status (created, read) VALUES ('2016-02-28 09:00:58.828541-03', true);
INSERT INTO status (created, read) VALUES ('2016-02-28 09:01:00.809748-03', true);
INSERT INTO status (created, read) VALUES ('2016-02-28 09:01:03.14392-03', true);
INSERT INTO status (created, read) VALUES ('2016-02-28 09:01:05.506604-03', true);
INSERT INTO status (created, read) VALUES ('2016-02-28 09:01:07.606256-03', true);
INSERT INTO status (created, read) VALUES ('2016-02-28 09:01:09.986617-03', true);
INSERT INTO status (created, read) VALUES ('2016-02-28 09:01:12.275312-03', true);
INSERT INTO status (created, read) VALUES ('2016-02-28 09:01:14.194565-03', true);
INSERT INTO status (created, read) VALUES ('2016-02-28 09:01:16.429208-03', true);
INSERT INTO status (created, read) VALUES ('2016-02-28 09:01:18.657266-03', true);
INSERT INTO status (created, read) VALUES ('2016-02-28 09:01:20.877406-03', true);
INSERT INTO status (created, read) VALUES ('2016-02-28 09:01:23.122152-03', true);
INSERT INTO status (created, read) VALUES ('2016-02-29 10:37:25.036703-03', true);
INSERT INTO status (created, read) VALUES ('2016-02-29 11:07:10.564814-03', true);
INSERT INTO status (created, read) VALUES ('2016-02-29 11:47:38.78204-03', false);
INSERT INTO status (created, read) VALUES ('2016-02-29 11:56:49.895785-03', true);
INSERT INTO status (created, read) VALUES ('2016-02-29 12:46:07.906734-03', false);
INSERT INTO status (created, read) VALUES ('2016-02-29 17:19:09.776746-03', true);
INSERT INTO status (created, read) VALUES ('2016-02-29 17:24:07.145661-03', false);
INSERT INTO status (created, read) VALUES ('2016-02-29 21:11:02.558749-03', true);
INSERT INTO status (created, read) VALUES ('2016-03-01 09:00:00.871397-03', true);
INSERT INTO status (created, read) VALUES ('2016-03-01 10:46:17.66168-03', true);
INSERT INTO status (created, read) VALUES ('2016-03-01 14:12:43.717506-03', true);
INSERT INTO status (created, read) VALUES ('2016-03-02 09:00:04.303278-03', true);
INSERT INTO status (created, read) VALUES ('2016-03-02 17:33:16.196374-03', true);
INSERT INTO status (created, read) VALUES ('2016-03-02 17:36:29.84208-03', true);
INSERT INTO status (created, read) VALUES ('2016-03-03 09:00:01.929879-03', true);
INSERT INTO status (created, read) VALUES ('2016-03-03 09:00:01.940345-03', true);
INSERT INTO status (created, read) VALUES ('2016-03-04 09:00:03.120712-03', true);
INSERT INTO status (created, read) VALUES ('2016-03-04 10:59:01.651798-03', true);
INSERT INTO status (created, read) VALUES ('2016-03-04 17:06:27.565846-03', true);
INSERT INTO status (created, read) VALUES ('2016-03-04 17:07:00.258593-03', true);
INSERT INTO status (created, read) VALUES ('2016-03-04 17:30:23.126116-03', true);
INSERT INTO status (created, read) VALUES ('2016-03-04 17:43:41.512822-03', true);
INSERT INTO status (created, read) VALUES ('2016-03-05 09:00:13.42016-03', true);
INSERT INTO status (created, read) VALUES ('2016-03-06 09:00:05.610203-03', true);
INSERT INTO status (created, read) VALUES ('2016-03-07 16:33:58.102805-03', false);
INSERT INTO status (created, read) VALUES ('2016-03-07 16:53:48.820488-03', false);
INSERT INTO status (created, read) VALUES ('2016-03-07 18:51:44.182288-03', false);
INSERT INTO status (created, read) VALUES ('2016-03-09 09:00:04.369842-03', true);
INSERT INTO status (created, read) VALUES ('2016-03-09 09:00:04.380432-03', true);
INSERT INTO status (created, read) VALUES ('2016-03-09 09:00:04.450373-03', true);
INSERT INTO status (created, read) VALUES ('2016-03-09 09:00:04.598239-03', true);
INSERT INTO status (created, read) VALUES ('2016-03-09 09:00:04.7075-03', true);
INSERT INTO status (created, read) VALUES ('2016-03-10 09:00:00.923048-03', true);
INSERT INTO status (created, read) VALUES ('2016-03-11 09:00:01.02605-03', true);
INSERT INTO status (created, read) VALUES ('2016-03-11 16:34:43.341189-03', true);
INSERT INTO status (created, read) VALUES ('2016-03-11 18:39:02.517519-03', true);
INSERT INTO status (created, read) VALUES ('2016-03-11 18:39:02.535953-03', true);
INSERT INTO status (created, read) VALUES ('2016-03-15 09:00:01.405166-03', true);
INSERT INTO status (created, read) VALUES ('2016-03-15 12:08:51.729326-03', true);
INSERT INTO status (created, read) VALUES ('2016-03-16 09:00:01.594785-03', true);
INSERT INTO status (created, read) VALUES ('2016-03-17 09:00:01.189489-03', true);
INSERT INTO status (created, read) VALUES ('2016-03-18 09:00:01.105867-03', true);
INSERT INTO status (created, read) VALUES ('2016-03-18 15:32:03.153641-03', false);
INSERT INTO status (created, read) VALUES ('2016-03-21 10:21:18.110903-03', false);
INSERT INTO status (created, read) VALUES ('2016-03-22 10:34:29.630107-03', false);
The grouped data might look like this:
min_created;max_created;read;count
"2016-02-23 14:53:39.668225-03";"2016-02-29 11:07:10.564814-03";TRUE;23
"2016-02-29 11:47:38.78204-03";"2016-02-29 11:47:38.78204-03";FALSE;1
"2016-02-29 11:56:49.895785-03";"2016-02-29 11:56:49.895785-03";TRUE;1
"2016-02-29 12:46:07.906734-03";"2016-02-29 12:46:07.906734-03";FALSE;1
"2016-02-29 17:19:09.776746-03";"2016-02-29 17:19:09.776746-03";TRUE;1
"2016-02-29 17:24:07.145661-03";"2016-02-29 17:24:07.145661-03";FALSE;1
"2016-02-29 21:11:02.558749-03";"2016-03-06 09:00:05.610203-03";TRUE;17
"2016-03-07 16:33:58.102805-03";"2016-03-07 18:51:44.182288-03";FALSE;3
"2016-03-09 09:00:04.369842-03";"2016-03-18 09:00:01.105867-03";TRUE;15
"2016-03-18 15:32:03.153641-03";"2016-03-22 10:34:29.630107-03";FALSE;3
And the answer to the what's the greatest interval of dates between read status should be:
"2016-03-09 09:00:04.369842-03";"2016-03-18 09:00:01.105867-03"
How to get this result using SQL?