Here is the solution I've arrived at by making use of the OVERLAPS() function, let me know if you can improve this!
Schema (PostgreSQL v11)
create table sensor_telemetry(
datetime TIMESTAMPTZ,
sensor_id int,
reading bigint
);
insert into sensor_telemetry (datetime, sensor_id, reading) values
('2020-01-01T00:00:00Z', 5, 1),
('2020-01-01T00:00:01Z', 5, 2),
('2020-01-01T00:00:02Z', 5, 3),
('2020-01-01T00:00:03Z', 5, 4),
('2020-01-01T00:00:04Z', 5, 5),
('2020-01-01T00:00:05Z', 5, 6),
('2020-01-01T00:00:06Z', 5, 7),
('2020-01-01T00:00:07Z', 5, 8),
('2020-01-01T00:00:08Z', 5, 9),
('2020-01-01T00:00:09Z', 5, 10),
('2020-01-01T00:00:10Z', 5, 11),
('2020-01-01T00:00:11Z', 5, 12),
('2020-01-01T00:00:12Z', 5, 13),
('2020-01-01T00:00:13Z', 5, 14),
('2020-01-01T00:00:14Z', 5, 15),
('2020-01-01T00:00:15Z', 5, 16),
('2020-01-01T00:00:16Z', 5, 17),
('2020-01-01T00:00:17Z', 5, 18),
('2020-01-01T00:00:18Z', 5, 19),
('2020-01-01T00:00:19Z', 5, 20),
('2020-01-01T00:00:20Z', 5, 21),
('2020-01-01T00:00:21Z', 5, 22),
('2020-01-01T00:00:22Z', 5, 23),
('2020-01-01T00:00:23Z', 5, 24),
('2020-01-01T00:00:24Z', 5, 25),
('2020-01-01T00:00:25Z', 5, 26),
('2020-01-01T00:00:26Z', 5, 27),
('2020-01-01T00:00:27Z', 5, 28),
('2020-01-01T00:00:28Z', 5, 29),
('2020-01-01T00:00:29Z', 5, 30),
('2020-01-01T00:00:30Z', 5, 31),
('2020-01-01T00:00:31Z', 5, 32),
('2020-01-01T00:00:32Z', 5, 33),
('2020-01-01T00:00:33Z', 5, 34),
('2020-01-01T00:00:34Z', 5, 35),
('2020-01-01T00:00:35Z', 5, 36),
('2020-01-01T00:00:36Z', 5, 37),
('2020-01-01T00:00:37Z', 5, 38),
('2020-01-01T00:00:38Z', 5, 39),
('2020-01-01T00:00:39Z', 5, 40),
('2020-01-01T00:00:40Z', 5, 41),
('2020-01-01T00:00:41Z', 5, 42),
('2020-01-01T00:00:42Z', 5, 43),
('2020-01-01T00:00:43Z', 5, 44),
('2020-01-01T00:00:44Z', 5, 45),
('2020-01-01T00:00:45Z', 5, 46),
('2020-01-01T00:00:46Z', 5, 47),
('2020-01-01T00:00:47Z', 5, 48),
('2020-01-01T00:00:48Z', 5, 49),
('2020-01-01T00:00:49Z', 5, 50),
('2020-01-01T00:00:50Z', 5, 51),
('2020-01-01T00:00:51Z', 5, 52),
('2020-01-01T00:00:52Z', 5, 53),
('2020-01-01T00:00:53Z', 5, 54),
('2020-01-01T00:00:54Z', 5, 55),
('2020-01-01T00:00:55Z', 5, 56),
('2020-01-01T00:00:56Z', 5, 57),
('2020-01-01T00:00:57Z', 5, 58),
('2020-01-01T00:00:58Z', 5, 59),
('2020-01-01T00:00:59Z', 5, 60),
('2020-01-01T00:01:00Z', 5, 61),
('2020-01-01T00:01:01Z', 5, 62),
('2020-01-01T00:01:02Z', 5, 63),
('2020-01-01T00:01:03Z', 5, 64),
('2020-01-01T00:01:04Z', 5, 65),
('2020-01-01T00:01:05Z', 5, 66),
('2020-01-01T00:01:06Z', 5, 67),
('2020-01-01T00:01:07Z', 5, 68),
('2020-01-01T00:01:08Z', 5, 69),
('2020-01-01T00:01:09Z', 5, 70),
('2020-01-01T00:01:10Z', 5, 71),
('2020-01-01T00:01:11Z', 5, 72),
('2020-01-01T00:01:12Z', 5, 73),
('2020-01-01T00:01:13Z', 5, 74),
('2020-01-01T00:01:14Z', 5, 75),
('2020-01-01T00:01:15Z', 5, 76),
('2020-01-01T00:01:16Z', 5, 77),
('2020-01-01T00:01:17Z', 5, 78),
('2020-01-01T00:01:18Z', 5, 79),
('2020-01-01T00:01:19Z', 5, 80),
('2020-01-01T00:01:20Z', 5, 81),
('2020-01-01T00:01:21Z', 5, 82),
('2020-01-01T00:01:22Z', 5, 83),
('2020-01-01T00:01:23Z', 5, 84),
('2020-01-01T00:01:24Z', 5, 85),
('2020-01-01T00:01:25Z', 5, 86),
('2020-01-01T00:01:26Z', 5, 87),
('2020-01-01T00:01:27Z', 5, 88),
('2020-01-01T00:01:28Z', 5, 89),
('2020-01-01T00:01:29Z', 5, 90),
('2020-01-01T00:01:30Z', 5, 91),
('2020-01-01T00:01:31Z', 5, 92),
('2020-01-01T00:01:32Z', 5, 93),
('2020-01-01T00:01:33Z', 5, 94),
('2020-01-01T00:01:34Z', 5, 95),
('2020-01-01T00:01:35Z', 5, 96),
('2020-01-01T00:01:36Z', 5, 97),
('2020-01-01T00:01:37Z', 5, 98),
('2020-01-01T00:01:38Z', 5, 99),
('2020-01-01T00:01:39Z', 5, 100),
('2020-01-01T00:01:40Z', 5, 101),
('2020-01-01T00:01:41Z', 5, 102),
('2020-01-01T00:01:42Z', 5, 103),
('2020-01-01T00:01:43Z', 5, 104),
('2020-01-01T00:01:44Z', 5, 105),
('2020-01-01T00:01:45Z', 5, 106),
('2020-01-01T00:01:46Z', 5, 107),
('2020-01-01T00:01:47Z', 5, 108),
('2020-01-01T00:01:48Z', 5, 109),
('2020-01-01T00:01:49Z', 5, 110),
('2020-01-01T00:01:50Z', 5, 111),
('2020-01-01T00:01:51Z', 5, 112),
('2020-01-01T00:01:52Z', 5, 113),
('2020-01-01T00:01:53Z', 5, 114),
('2020-01-01T00:01:54Z', 5, 115),
('2020-01-01T00:01:55Z', 5, 116),
('2020-01-01T00:01:56Z', 5, 117),
('2020-01-01T00:01:57Z', 5, 118),
('2020-01-01T00:01:58Z', 5, 119),
('2020-01-01T00:01:59Z', 5, 120),
('2020-01-01T00:02:00Z', 5, 121)
;
create table status(
sensor_id int,
status_id int,
start_date timestamptz,
end_date timestamptz,
status varchar(15)
);
insert into status (sensor_id, status_id, start_date, end_date, status) values
(5, 8, '2020-01-01T00:00:00Z', '2020-01-01T00:01:30Z', 'stationary'),
(5, 9, '2020-01-01T00:01:30Z', '2020-01-01T00:01:45Z', 'in_motion'),
(5, 10, '2020-01-01T00:01:45Z', '2020-01-01T00:02:00Z', 'stationary');
create table sensor(
sensor_id int,
location_id int
);
insert into sensor (sensor_id, location_id) values
(5, 16);
create table shift(
shift varchar(15),
location_id int,
occurence_id int,
start_date timestamptz,
end_date timestamptz
);
insert into shift (shift, location_id, occurence_id, start_date, end_date) values
('Shift A', 16, 123, '2020-01-01T00:00:00Z', '2020-01-01T00:01:00Z'),
('Shift B', 16, 124, '2020-01-01T00:01:00Z', '2020-01-01T00:02:00Z');
Query #1
with statuses as (
select
stat.sensor_id,
stat.status,
stat.status_id,
stat.start_date,
stat.end_date,
sens.location_id
from status stat
inner join sensor sens on (sens.sensor_id = stat.sensor_id)
where stat.sensor_id=5
),
shift_status as (
select
sa.sensor_id,
s.shift,
s.occurence_id,
s.start_date as shift_start,
s.end_date as shift_end,
sa.status,
sa.status_id,
sa.start_date as status_start,
sa.end_date as status_end
from shift s
inner join statuses sa on
(sa.location_id = s.location_id
and (sa.start_date, sa.end_date) OVERLAPS (s.start_date, s.end_date))
),
phases as (
select
sensor_id,
shift,
occurence_id,
status,
status_id,
GREATEST(shift_start, status_start) as start_date,
LEAST(shift_end, status_end) as end_date
from shift_status
order by start_date
)
select
st.sensor_id,
shift,
occurence_id,
status,
status_id,
MIN(datetime) as start_date,
MAX(datetime) as end_date,
MIN(reading) as reading_start,
MAX(reading) as reading_end
from sensor_telemetry st
inner join phases p on
(st.sensor_id = p.sensor_id
and (p.start_date, p.end_date) overlaps (st.datetime, st.datetime))
group by st.sensor_id, shift, occurence_id, status, status_id
order by start_date;
sensor_id |
shift |
occurence_id |
status |
status_id |
start_date |
end_date |
reading_start |
reading_end |
5 |
Shift A |
123 |
stationary |
8 |
2020-01-01T00:00:00.000Z |
2020-01-01T00:00:59.000Z |
1 |
60 |
5 |
Shift B |
124 |
stationary |
8 |
2020-01-01T00:01:00.000Z |
2020-01-01T00:01:29.000Z |
61 |
90 |
5 |
Shift B |
124 |
in_motion |
9 |
2020-01-01T00:01:30.000Z |
2020-01-01T00:01:44.000Z |
91 |
105 |
5 |
Shift B |
124 |
stationary |
10 |
2020-01-01T00:01:45.000Z |
2020-01-01T00:01:59.000Z |
106 |
120 |
View on DB Fiddle
location_id
in the telemetry table!