1

I am looking for a standard method, a library or maybe best practice approach that can generate a time-interval/state-duration report from a time/state series.

This seems like a common reporting requirement for time/state data, and I would assume there is framework support for this.

I am specifically seeking a function or library to achieve this, rather than a custom SQL solution, but maybe there is only a custom way to do it.

Below, I provide (in postgres) a simplified structure and sample data to facilitate the discussion.

Creating the input table

-- Create the time_state table
CREATE TABLE "time_state" (
  "state_time" TIMESTAMP WITH TIME ZONE NOT NULL,
  "state_name" VARCHAR(50) NOT NULL
);

Adding example input data

INSERT INTO "time_state" ("state_time", "state_name") VALUES
('2023-07-01 21:00:00+00', 'OFF'),
('2023-07-01 21:15:00+00', 'IDLE'),
('2023-07-01 21:30:00+00', 'HEATING'),
('2023-07-01 21:45:00+00', 'COOLING'),
('2023-07-01 22:00:00+00', 'FAULT'),
('2023-07-01 22:15:00+00', 'OFF'),
('2023-07-01 22:30:00+00', 'IDLE'),
('2023-07-01 22:45:00+00', 'HEATING'),
('2023-07-01 23:00:00+00', 'COOLING'),
('2023-07-01 23:15:00+00', 'FAULT'),
('2023-07-01 23:30:00+00', 'OFF'),
('2023-07-01 23:45:00+00', 'IDLE'),

('2023-07-02 00:00:00+00', 'HEATING'),
('2023-07-02 00:15:00+00', 'COOLING'),
('2023-07-02 00:30:00+00', 'FAULT'),
('2023-07-02 00:45:00+00', 'OFF'),
('2023-07-02 01:00:00+00', 'IDLE'),
('2023-07-02 01:15:00+00', 'HEATING'),
('2023-07-02 01:30:00+00', 'COOLING'),
('2023-07-02 01:45:00+00', 'FAULT'),
('2023-07-02 02:00:00+00', 'OFF'),
('2023-07-02 02:15:00+00', 'IDLE'),
('2023-07-02 02:30:00+00', 'HEATING'),
('2023-07-02 02:45:00+00', 'COOLING'),
('2023-07-02 03:00:00+00', 'FAULT'),
('2023-07-02 03:15:00+00', 'OFF'),
('2023-07-02 03:30:00+00', 'IDLE'),
('2023-07-02 03:45:00+00', 'HEATING'),
('2023-07-02 04:00:00+00', 'COOLING');

Result of the report that shows the duration of each state within each hour should be then:

date_with_hour  state_name  state_duration_in_minutes
2023-07-01 21:00:00 COOLING 15.0
2023-07-01 21:00:00 HEATING 15.0
2023-07-01 21:00:00 IDLE    15.0
2023-07-01 21:00:00 OFF     15.0
2023-07-01 22:00:00 COOLING 15.0
2023-07-01 22:00:00 FAULT   15.0
2023-07-01 22:00:00 HEATING 15.0
2023-07-01 22:00:00 IDLE    15.0
2023-07-01 23:00:00 COOLING 15.0
2023-07-01 23:00:00 FAULT   15.0
2023-07-01 23:00:00 HEATING 15.0
2023-07-01 23:00:00 IDLE    15.0
2023-07-02 00:00:00 COOLING 15.0
2023-07-02 00:00:00 FAULT   15.0
2023-07-02 00:00:00 HEATING 15.0
2023-07-02 00:00:00 IDLE    15.0

Thanks

Dave

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.