Generate Time Interval

From a table of products, let us consider the rows with product_id = 1002.

product_id	valid_from	price
1002	2019-01-05	100
1002	2019-02-08	105
1002	2019-02-17	103

This means the price of the product with product_id = 1002 is 100$
from 2019-01-05 to 2019-02-07.

The change in the content of the table attributes can be illustrated by time periods:

Fig. Time Periods

	100 $		105 $		103 $
2019-01-05		2019-02-08		2019-02-17		9999-12-31

To answer the question about the price on 2019-02-01 with a SQL query an additional attribute valid_to would be helpful.

product_id	valid_from	valid_to	price
1002	2019-05-01	2019-02-08	100
1002	2019-02-08	2019-02-17	105
1002	2019-02-17	9999-12-31	103

• Valid_to is the date from which the price is no longer valid.

PostgreSQL code about the price on 2019-02-01:

select price from product_2 
where '2019-02-01' 
    between valid_from and valid_to - interval '1 day';

The table product_2 is just a view on table product_1:

create view product_2 as
select *, 
  coalesce(lead(valid_from) over(partition by product_id 
      order by valid_from), '9999-12-31') as valid_to
from product_1;

If we have a second date registered_from on which valid_from also depends, we are talking about the bitemporal case. A view with the additional fields “registered_to” and “valid_to” can also be created in this case.
But that’s not merely a simple analogy. The transition to two-dimensional time means more than looking at one-dimensional times twice.

The time periods according to the figure “Time Periods” which spanning one time dimension become time areas which spanning two time dimensions in the bitemporal case.

To read about the bitemporal case continue here:

Generate Bitemporal Intervals