I have data which is shown below.

and I want to query data into this structure

My data timeline data is split into many parts, but I want to group it into one.

CodePudding user response：

demo
idea: construct a range, test (startTime,endTime) belong to one specific timestamptz range or not,
then group by stationname, status, range(startTime, endTime) belong to.

I would use timestamptz type for startTime, endTime, tstzrange. If you construct timerange, then range (23:00,0:00) is invalid. table structure:

CREATE TABLE test102 (
    stationName text,
    status text,
    startTime timestamptz,
    endTime timestamptz
);

then query:

WITH cte AS (
    SELECT
        test102.*,
        tstzrange(startTime, endTime) tstzrange_se,
        tstzrange(i - interval '1 hour', i)
    FROM
        test102,
        generate_series('2022-05-02'::timestamp, '2022-05-02'::timestamp   interval '24 hour', interval '1 hour') i
)
SELECT
    stationname,
    status,
    min(startTime),
    max(endTime)
FROM
    cte
WHERE
    tstzrange_se <@ tstzrange
GROUP BY
    1,
    2,
    tstzrange;