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.
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.
To read about the bitemporal case continue here: