How to merge history when joining tables?

Question

PostgreSQL

Good afternoon, there are two tables:

Table1(id, param1, param2, date_from, date_to):
(1, 'z', 55, '05/01/2010 12:30:20', '05/17/2010 13:10:14' ),
(1, 'c', null, '17.05.2010 13:10:15', '18.01.2010 04:13:15'),
(1, 'c', 25, '18.01.2010 04:13 :16', '01.01.9999 00:00:00');

Table2(id, param3, date_from, date_to):
(1, 15, '04/01/2010 12:30:20', '05/02/2010 13:10:14'),
(1, 35, '05/02/2010 13: 10:15', '01.01.9999 00:00:00');
It is necessary that when merging 2 tables there are fields containing the history of each parameter change. I.e:

Select(id, param1, param2, param3, date_from, date_to):
(1, null, null, 10, '01.04.2010 12:30:20', '01.05.2010 12:30:19'),
(1, 'z', 55, 15, '01.05.2010 12:30:20', '02.05.2010 13:10:14'),
(1, 'z', 55, 35, '02.05.2010 13:10:15', '17.05.2010 13:10:14'),
(1, 'c', null, 35, '17.05.2010 13:10:15', '18.01.2010 04:13:15'),
(1, 'c', 25, 35, '18.01.2010 04:13:16', '01.01.9999 00:00:00');

Where records from two tables did not intersect in time (or part of the time) null should be displayed.

Here is my request:

WITH dates_from AS (
  SELECT id, date_from FROM Table1
  UNION
  SELECT id, date_to + interval '1 second' FROM Table1
  UNION
  SELECT id, date_from FROM Table2
  UNION
  SELECT id, date_to + interval '1 second' FROM Table2
),
dates_to AS (
  SELECT id, date_to FROM Table1
  UNION
  SELECT id, date_from - interval '1 second' FROM Table1
  UNION
  SELECT id, date_to FROM Table2
  UNION
  SELECT id, date_from - interval '1 second' FROM Table2
),
ranges AS (
  SELECT df.id, date_from, MIN(date_to) AS date_to
  FROM dates_from df
  JOIN dates_to dt ON dt.id = df.id AND dt.date_to > df.date_from
  GROUP BY df.id, date_from
)
SELECT r.id,
       t1.param1, t1.param2, t2.param3,
       r.date_from, r.date_to
FROM ranges r
LEFT JOIN Table1 t1 ON t1.id = r.id AND t1.date_from <= r.date_to and t1.date_to >= r.date_from
LEFT JOIN Table2 t2 ON t2.id = r.id AND t2.date_from <= r.date_to and t2.date_to >= r.date_from
ORDER BY r.id, r.date_from

Since the date is set incorrectly in the first table date_from > date_to the output is not correct, how to take this into account?

Answer 1

CASE WHEN date_from > date_to THEN date_to
ELSE date_from
END AS date_from