Calculating tsrange based on parent/child created dates Given the table:

CREATE TABLE node (
    id serial PRIMARY KEY,
    created timestamp without time zone NOT null,
    path ltree NOT null UNIQUE
);


And the records:

INSERT INTO node (id, path, created)
VALUES
  (1, '1', '2010-01-01 12:00'),
  (2, '1.2', '2010-01-01 13:00'),
  (3, '1.2.3', '2010-01-01 14:00');


How do I get the valid range for each node based on the node's created date and its' parent's created date?

For instance, the ranges for the given path 1.2.3 would be:

id | path  | valid
------------------------------------------------------------------
 1 |     1 | tsrange('2010-01-01 12:00', '2010-01-01 13:00', '[)')
 2 |   1.2 | tsrange('2010-01-01 13:00', '2010-01-01 14:00', '[)')
 3 | 1.2.3 | tsrange('2010-01-01 14:00', 'infinity', '[)')
I cannot think of a smarter way than a self-join with all nodes that have a path just one element longer:

SELECT n1.id, n1.path,
       tsrange(n1.created, max(n2.created)) AS valid
FROM node AS n1
   LEFT JOIN node AS n2
      ON n2.path ~ (n1.path::text || '.*{1}')::lquery
GROUP BY n1.id;

 id | path  |                     valid                     
----+-------+-----------------------------------------------
  1 | 1     | ["2010-01-01 12:00:00","2010-01-01 13:00:00")
  2 | 1.2   | ["2010-01-01 13:00:00","2010-01-01 14:00:00")
  3 | 1.2.3 | ["2010-01-01 14:00:00",)
(3 rows)