From a table of products, let us consider the rows with product_id = 1002.
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:
|100 $||105 $||103 $|
To answer the question about the price on 2019-02-01 with a SQL query an additional attribute valid_to would be helpful.
- 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: