Dans la suite des articles présentant des fonctionnalités du langage SQL disponible dans PostgreSQL (pas forcément présentes dans la norme SQL), nous allons parler d’une fonctionnalité SQL intéressante, les plages de valeurs (RANGE), je vous conseille de lire, si vous ne l’avez pas déjà fait, l’article sur les contraintes d’exclusion

Qu’est-ce qu’une plage de valeurs ?

Une plage de valeurs représente un nombre de valeurs plus ou moins grand en une seule valeur intervalle. Il existe plusieurs types de données d’intervalles dérivés de types scalaires.

• integer : int4range
• bigint : int8range
• numeric : numrange
• timestamp without time zone : tsrange
• timestamp with time zone : tstzrange
• date : daterange

Un intervalle de valeurs a deux bornes : une borne inférieure et une borne supérieure. Les valeurs peuvent être incluses ou non, selon les bornes utilisées.

La borne inclusive est représentée par les caractères [ (borne inférieure) et ] (borne supérieure) et les bornes exclusives par ( (borne inférieure) et ) (borne supérieure).

Construction d’une plage de valeurs

Afin de construire une donnée de l’un de ces types, il est possible d’utiliser un litéral avec un typage explicite, par exemple :

SELECT '[1,10)'::int4range;
┌───────────┐
│ int4range │
├───────────┤
│ [1,10)    │
└───────────┘

ou :

SELECT '[2020-06-26,2020-06-28)'::daterange;
┌─────────────────────────┐
│        daterange        │
├─────────────────────────┤
│ [2020-06-26,2020-06-28) │
└─────────────────────────┘

Il est possible de ne pas renseigner une des deux bornes, incluant dans la plage de valeurs toutes les valeurs plus petite ou plus grande que la borne indiquée, par exemple :

SELECT '[1,)'::int4range;
┌───────────┐
│ int4range │
├───────────┤
│ [1,)      │
└───────────┘

ou :

SELECT '[,2020-06-26)'::daterange;
┌───────────────┐
│   daterange   │
├───────────────┤
│ (,2020-06-26) │
└───────────────┘

Par ailleurs, la construction d’une plage de valeurs peut se faire à l’aide d’une fonction spécifique à chaque type, le constructeur, qui accepte 2 ou 3 paramètres.

Les deux premiers paramètres sont respectivement la borne inférieure et supérieure et le troisième paramètre, optionnel, représente le mode de bornage : par défaut elle est inclusive pour la borne inférieure et exclusive pour la borne supérieure.

Dans l’exemple ci-après, l’intervalle va de la valeur 10 incluse à la valeur 20 exclue :

SELECT int4range(10, 20);
┌───────────┐
│ int4range │
├───────────┤
│ [10,20)   │
└───────────┘

Si on souhaite modifier le bornage par défaut, on utilisera le troisième paramètre, dans ce cas les bornes sont inclusives :

SELECT numrange(10.5, 20.2, '[]');
┌─────────────┐
│  numrange   │
├─────────────┤
│ [10.5,20.2] │
└─────────────┘

Cependant, il y a des exceptions :

SELECT daterange('2020-06-25','2020-06-27', '(]');
        daterange
┌─────────────────────────┐
│        daterange        │
├─────────────────────────┤
│ [2020-06-26,2020-06-28) │
└─────────────────────────┘

Comme le montre l’exemple précédent, l’affichage de la valeur ne correspond pas au paramètre '(]', la raison est que int4range, int8range, daterange sont des types intervalles de valeurs discrètes, la valeur est donc convertie sous la forme canonique '[)'.

Les opérateurs

En plus des opérateurs classiques (=,<> …), d’autres sont disponibles, en particulier l’opérateur permettant de tester le chevauchement de deux plages.

Voici quelques opérateurs utiles dans la gestion des plages de temps :

• Égalité entre 2 intervalles (=):

SELECT tsrange('2020-06-26 18:00','2020-06-26 22:00')
    =  tsrange('2020-06-26 18:00','2020-06-26 22:00')
┌────┐
│ eq │
├────┤
│ t  │
└────┘

ou :

SELECT tsrange('2020-06-26 18:00','2020-06-26 22:00')
    =  tsrange('2020-06-26 18:01','2020-06-26 22:00') eq
┌────┐
│ eq │
├────┤
│ f  │
└────┘

• Supériorité / infériorité avec > et <. Les comparaisons s’effectuent sur la borne inférieure de l’intervalle :

SELECT tsrange('2020-06-26 18:01','2020-06-26 18:02')
    >  tsrange('2020-06-26 17:59','2020-06-26 22:00') as sup
┌─────┐
│ sup │
├─────┤
│ t   │
└─────┘

ou :

SELECT tsrange('2020-06-26 18:01','2020-06-26 18:02')
    <  tsrange('2020-06-26 17:59','2020-06-26 22:00') as inf
┌─────┐
│ inf │
├─────┤
│ f   │
└─────┘

• Chevauchement &&. Les plages testées ont-elles des valeurs communes :

SELECT tsrange('2020-06-26 18:00','2020-06-26 22:00')
    && tsrange('2020-06-26 20:00','2020-06-26 22:00') as overlap;
┌─────────┐
│ overlap │
├─────────┤
│ t       │
└─────────┘

• Contient @>. La plage de gauche contient-elle la plage de droite :

SELECT tsrange('2020-06-26 12:00','2020-06-26 22:00')
    @> tsrange('2020-06-26 16:00','2020-06-26 18:00') as contains_range;
┌────────────────┐
│ contains_range │
├────────────────┤
│ t              │
└────────────────┘

ou :

SELECT tsrange('2020-06-26 12:00','2020-06-26 22:00')
    @> '2020-06-26 16:00'::timestamp as contains_element;
┌──────────────────┐
│ contains_element │
├──────────────────┤
│ t                │
└──────────────────┘

• Contenu par <@. La plage de gauche est-elle contenue dans la plage de droite :

SELECT tsrange('2020-06-26 18:00','2020-06-26 19:00')
    <@ tsrange('2020-06-26 16:00','2020-06-26 20:00') as range_is_contained_by
┌───────────────────────┐
│ range_is_contained_by │
├───────────────────────┤
│ t                     │
└───────────────────────┘

ou :

SELECT '2020-06-26 18:00'::timestamp
    <@ tsrange('2020-06-26 16:00','2020-06-26 20:00') as element_is_contained_by;
┌─────────────────────────┐
│ element_is_contained_by │
├─────────────────────────┤
│ t                       │
└─────────────────────────┘

Vous trouverez l’ensemble des informations complémentaires dans la documentation sur les opérateurs.

Les fonctions

Après les opérateurs, voyons à présent les fonctions disponibles pour ces types de données.

• Limite basse de l’intervalle : lower() :

SELECT lower(tsrange('2020-06-26 18:01','2020-06-26 18:02'));
┌─────────────────────┐
│        lower        │
├─────────────────────┤
│ 2020-06-26 18:01:00 │
└─────────────────────┘

• Limite haute de l’intervalle : upper() :

SELECT upper(tsrange('2020-06-26 18:01','2020-06-26 18:02'));
┌─────────────────────┐
│        upper        │
├─────────────────────┤
│ 2020-06-26 18:02:00 │
└─────────────────────┘

• La limite inférieure de l’intervalle est-elle incluse : lower_inc() ?

SELECT lower_inc(tsrange('2020-06-26 18:01','2020-06-26 18:02'));
┌───────────┐
│ lower_inc │
├───────────┤
│ t         │
└───────────┘

ou :

SELECT lower_inc(tsrange('2020-06-26 18:01','2020-06-26 18:02', '()'));
┌───────────┐
│ lower_inc │
├───────────┤
│ f         │
└───────────┘

• La limite supérieure de l’intervalle est-elle incluse : upper_inc() ? Par défaut la borne supérieure n’est pas incluse :

SELECT upper_inc(tsrange('2020-06-26 18:01','2020-06-26 18:02'));
┌───────────┐
│ upper_inc │
├───────────┤
│ f         │
└───────────┘

Donc pour forcer la limite supérieure incluse, il faut le préciser par le troisième paramètre du constructeur qui représente le mode de bornage :

SELECT upper_inc(tsrange('2020-06-26 18:01','2020-06-26 18:02', '[]'));
┌───────────┐
│ upper_inc │
├───────────┤
│ t         │
└───────────┘

• La limite inférieure est-elle infinie (sans borne) : lower_inf() ?

SELECT lower_inf('(,2020-06-26 18:02)'::TSTZRANGE);
┌───────────┐
│ lower_inf │
├───────────┤
│ t         │
└───────────┘

• La limite supérieure est infinie (sans borne) : upper_inf() ?

 SELECT upper_inf('(2020-06-26 18:01,)'::TSTZRANGE);
┌───────────┐
│ upper_inf │
├───────────┤
│ t         │
└───────────┘

• Fusion de plage : range_merge( p1, p2) : renvoie un intervalle incluant la plus petite et la plus grande valeur des deux intervalles :

SELECT range_merge('[2020-06-26 18:01,2020-06-26 18:02)'::TSTZRANGE
                   , '(2020-06-26 18:06,2020-06-26 20:00)'::TSTZRANGE);
┌─────────────────────────────────────────────────────┐
│                     range_merge                     │
├─────────────────────────────────────────────────────┤
│ ["2020-06-26 18:01:00+02","2020-06-26 20:00:00+02") │
└─────────────────────────────────────────────────────┘

Vous trouverez l’ensemble des informations complémentaires dans la documentation sur les fonctions et les types.

Pourquoi l’utiliser ?

Lors de la conception d’une application gérant les réservations pour la location de vélo par exemple, on sera amené à se poser la question: comment stockons-nous l’information des plages de locations ?

On peut imaginer le stockage de cette information par 2 colonnes start_time, end_time et la gestion de l’intégrité des données par déclencheur ou dans le code de l’application.

Une autre solution, l’utilisation d’un type range qui intègre le concept de chevauchement grâce à l’opérateur &&, avec l’extension btree_gist qui fournit des classes d’opérateurs utilisables par l’index GiST, il est facile de garantir l’unicité de l’intervalle avec une contrainte d’exclusion.

De cette manière le contrôle est directement fait par le type de données et de façon transactionnelle.

Conclusion

Cette première partie vous ayant présenté les plages de valeurs, pour illustrer l’intérêt de ce type, nous allons dans un second article passer à la pratique en prenant l’exemple d’une application dont la fonction est de gérer les réservations de la location de vélos.

Nous comparerons la mise en oeuvre et les performances d’une plage de dates et de la gestion d’intervalle avec l’utilisation de date de début et de fin utilisant les types scalaires traditionnels.