Generate Time Interval

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

table product_1

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.

table product_2
  • 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:

Generate Bitemporal Intervals

%d bloggers like this: