Generate Bitemporal Intervals

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

table product_1
product_idregistered_fromvalid_fromprice
10022019-01-012019-01-05100
10022019-02-092019-02-08105
10022019-02-102019-02-17103
10022019-02-122019-02-15102
10022019-03-022019-01-10103
10022019-04-012019-04-02111

This means that on 2019-01-01 it was registered that the price of the product with product_id = 1002 is 100$ as of 2019-01-05. Initially, this is valid indefinitely, that is, until 9999-12-31. Later, on 2019-02-09 it was registered that the 100$ price is valid only up to including 2019-02-07 and from 2019-02-08 the product costs 105$.

Two additional fields registered_to and valid_to would be helpful to formulate SQL code for the query of how the price was registered since 2019-02-28 for the valid date 2019-02-01.

In order to be able to map both the beginning and the end of the intervals for two periods, up to 11 rows in the table product_2 are required instead of the six rows in Table product_1. The maximum required rows in product_2 are never as much as twice
the amount of rows in product_1.

table product_2
product_idregistered_fromregistered_tovalid_fromvalid_toprice
10022019-01-012019-02-092019-01-059999-12-31100
10022019-02-099999-12-312019-01-052019-01-10100
10022019-02-092019-03-022019-01-102019-02-08100
10022019-02-092019-02-102019-02-089999-12-31105
10022019-02-102019-03-022019-02-082019-02-15105
10022019-02-102019-02-122019-02-152019-02-17105
10022019-02-102019-02-122019-02-179999-12-31103
10022019-02-122019-03-022019-02-159999-12-31102
10022019-03-022019-04-012019-01-109999-12-31103
10022019-04-019999-12-312019-01-102019-04-02103
10022019-04-019999-12-312019-04-029999-12-31111

Registered_to is the date of the next registration of the product_id, valid_to is the date from which the price is no longer valid under the current registration.

The table product_2 in graphic representation:

The registered time runs on the vertical axis reg.
The valid time runs on the horizontal axis val.
 two-dimensional timing diagram

9999-12-31
reg 100 $ 103 $ 111 $

2019-04-01
103 $

2019-03-02
100 $ 105 $ 102 $

2019-02-12
105 $ 103 $

2019-02-10
105 $

2019-02-09
100 $

2019-01-01

2019-01-05

2019-01-10

2019-02-08

2019-02-15

2019-02-17

2019-04-02

9999-12-31
val

PostgreSQL code to query the 2019-02-01 price as it was registered on 2019-02-28:

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

The table product_2 is just a view on table product_1:

create view product_2 as  
with product as ... 
select *,
    row_number() over (partition by product_id 
        order by registered_from) as rankReg,
    row_number() over (partition by product_id 
        order by valid_from, registered_from) as rankVal
from product_1
...
select a.product_id, 
    b.registered_from, ... '9999-12-31' ... as registered_to,    
    d.valid_from,      ... '9999-12-31' ... as valid_to, ....
    f.price    
from ...
     inner join product b on a.product_id = b.product_id and ...                          
     ...
     inner join product f on a.product_id = f.product_id and ...

Complete SQL code to generate bitemporal time intervals
Derivation of complete SQL code

The view requires the uniqueness of the date registered_from for each product_id. If necessary, the time type of registered_from must be more granular.

Conclusion

A view does not require any additional memory. Therefore, the memory requirement for the transition from time points to time intervals is insignificant even with bitemporal presentation.

Remark:
For performance reasons, one would not work with the view, but persistent tables and corresponding keys. With tuple time stamping and normalization, the memory requirement for this transition is basically unchanged even with persistence.

Translated with help of http://www.DeepL.com/Translator (free version)

%d bloggers like this: