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.

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

Fig. 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.

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.

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

%d bloggers like this: