product_id | valid_from | valid_to | price |
1002 | 2021-07-01 | 2021-07-05 | 100 |
1002 | 2021-07-07 | 2021-07-10 | 105 |
1002 | 2019-07-10 | 9999-12-31 | 103 |
The first row of the table means the price of the product with product_id = 1002 is $100 from 2021-07-01 to 2021-07-04, including the day 2021-07-04.
For the dates 2021-07-05 and 2021-07-06 there is no time slice in the product table for the product with the product_id 1002 containing the date. This need not be a mistake, but may be appropriate to the situation. However, a gaplessness of the time slices may well be desired.
To find all the gaps in the history of a product, it is enough to identify the time slices that do not have a successor. Only the time slice with the largest valid_to does not need a successor.
PostgreSQL code to determine any time slice except the latest without successor:
select * from ( select *, lead(valid_from) over(partition by product_id order by valid_from, valid_to) as valid_lead from product ) foo where valid_to <> valid_lead;