Completeness of Time Slices

product_idvalid_fromvalid_toprice
10022021-07-012021-07-05100
10022021-07-072021-07-10105
10022019-07-109999-12-31103
table product

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;
%d bloggers like this: