Is there a way to overlap a time series data table onto another using an sql query?

hoping I can find an answer to my conundrum here.

I have two tables in a Postgres DB and I'd like to overlap on onto the other.

Table A has 3 columns: "start time", "end time" and "state". In each row, the start time is equal to the end time of the preceding row.

Table B has has the same columns but the start & end times aren't adjacent and they do not overlap.

I want to overlap Table B onto Table A to create Table C.

Table C also has the start time of each row equal to the end time of the preceding row.

Here is an example to clarify:

Table A - Overlapee

State	Start Time	End Time
1	12:00:00 AM	12:10:00 AM
2	12:10:00 AM	12:20:00 AM
1	12:20:00 AM	12:30:00 AM
2	12:30:00 AM	12:40:00 AM
1	12:40:00 AM	12:50:00 AM
2	12:50:00 AM	1:00:00 AM

Table B - Overlaper

State	Start Time	End Time
5	12:05:00 AM	12:25:00 AM
6	12:31:00 AM	12:35:00 AM
5	12:40:00 AM	12:50:00 AM

Table C - Result of overlap

State	Start Time	End Time
1	12:00:00 AM	12:05:00 AM
5	12:05:00 AM	12:25:00 AM
1	12:25:00 AM	12:30:00 AM
2	12:30:00 AM	12:31:00 AM
6	12:31:00 AM	12:35:00 AM
2	12:35:00 AM	12:40:00 AM
5	12:40:00 AM	12:50:00 AM
2	12:50:00 AM	1:00:00 AM

As you can see, if the start time of a row in table B falls within a rows time range in table A , then the end time of the row in table A is replaced with the start time of the row in table B. Conversely, if the end time of a row in table B falls within a time range in table A, then the start time of the row in table A is replaced with the end time of the row in table B.

Secondly, If the start time and end time in Table A and B are exactly the same, then the row in B simply replaces that in A.

Thirdly, If the time range for a row in Table B falls within a time range for a row in Table A, then the Table A row is split into 2, with the end time of the first row being the start time of the Table B row and then start time of the second row would be the end time of the Table B row.

Lastly, the state must be preserved for the time range correctly.

Is it possible to accomplish this with an sql query?. I haven't found any similar questions or answers.

https://stackoverflow.com/questions/66592779/is-there-a-way-to-overlap-a-time-series-data-table-onto-another-using-an-sql-que March 12, 2021 at 09:08AM