Overlaps

In the SQL code examples under the above links, the command “overlap” occurs.

Description: PostgreSQL 9.0 functions date-time

Example SQL code for overlaps:

Select a.key,
    greatest(a.valid_from, b.valid_from) as valid_from,
    least(a.valid_to, b.valid_to)
    ...
from a join b on a.key = b.key
    and (a.valid_from, a.valid_to) overlaps 
        (b.valid_from, b.valid_to);

The expression (a.valid_from, a.valid_to) overlaps (b.valid_from, b.valid_to) is True if the time slice [a.valid_from, a.valid_to) of table a and the time slice [b.valid_from, b.valid_to) of table b overlap, i.e. have at least one chronological unit in common.
(‘2001-01-01’,  ‘2001-01-05’) overlaps (‘2001-01-04′,  ”2001-02-02’) is “True” because both time slices have the date ‘2001-01-04’ in common.

In the case of a.valid_to > a.valid_from and b.valid_to > b.valid_from, the example SQL can be written without using overlaps:

Select a.key,
    greatest(a.valid_from, b.valid_from) as valid_from,
    least(a.valid_to, b.valid_to
    ...
from a join b on a.key = b.key
    and a.valid_from < b.valid_to and b.valid_from < a.valid_to.

In a data warehouse, valid_from = valid_to is generally also permitted. The entity is then not valid at any time, but information about this entity can still be stored. The overlap command takes this into account so that this information is not lost.
Without overlaps, this case must be mapped explicitly:

Select a.key,
    greatest(a.valid_from, b.valid_from) as valid_from,
    least(a.valid_to, b.valid_to
    ...
from a join b on a.key = b.key
    and 
    (
        (a.valid_from < b.valid_to and b.valid_from < a.valid_to)
        or
        (a.valid_from = a.valid_to and a.valid_from >= b.valid_from 
             and a.valid_from < b.valid_to)
        or
        (b.valid_from = b.valid_to and b.valid_from >= a.valid_from 
             and b.valid_from < a.valid_to)
        or
        (a.valid_from = a.valid_to and b.valid_from = b.valid_to 
             and a.valid_from = b.valid_from)
    )