#postgresql
jeudi 31 mai 2018
Ce billet a été vu pour la première fois sur le blog de Synbioz le 31 May 2018 sous licence CC BY-NC-SA.

Translation temporelle

Cette semaine, je me suis essayé à un nouveau format d’article qui se présente sous la forme d’une nouvelle de Science-Fiction. Je tiens en passant à remercier Valentin Dumont pour ses illustrations. N’hésitez pas à laisser vos impressions dans la section commentaires ! Bonne lecture.

Le bon jour

« Le 30 mai ? C’est une blague !? D’où sort cette fichue date ? Ça n’a strictement aucun sens ! N’est-on pas fichu de calculer une simple translation temporelle, bon sang ! Ce n’est pas comme si je vous demandais de faire de l’algèbre quantique !
— Si vous faites référence aux groupes quantiques, intervint Jérémie, tels que présentés par Vladimir Drinfeld, Professeur, on peut toutefois y voir une certaine similitude, en extrapolant un peu — bon d’accord, beaucoup — si on les observe tous deux sous l’angle de la commutativité. Laissez-moi vous préciser mon point de vue, si vous me permettez d’enfoncer quelques portes ouvertes et de procéder à quelques simplifications de notre modèle mathématique afin que tout le monde ici puisse suivre le fil de mes explications. »

Et ce faisant, il s’installa devant la console qui faisait face à l’assemblée.

console

« La translation temporelle est chose aisée à concevoir, reprit-il, mais dépendant de tant de paramètres tous plus sensibles et intangibles les uns que les autres, qu’elle en devient extrêmement complexe à implémenter dans les faits. Pour illustrer mon propos en faisant abstraction de toute la complexité superflue pouvant entraver la compréhension de celui-ci, j’utiliserai un système de gestion de bases de données qui a connu ses heures de gloire dans les premières décennies de notre siècle et qui, encore de nos jours, reste couramment utilisé, notamment dans les domaines n’ayant pas l’utilité d’unités de calcul quantiques. Par chance, le fonctionnement interne de cet outil est en tout point similaire aux outils que nous utilisons réellement ; tout du moins pour le sujet qui nous intéresse ici. »

Ouvrant une session PostgreSQL sur sa console, Jérémie poursuivit.

« Si je vous demandais quel jour étions-nous il y a cinq mois de cela, il y a fort à parier que la majorité d’entre vous me répondrait le 31 décembre. Et vous auriez raison. Cela semble évident, mais si l’on y regarde de plus près, ça ne l’est pas tant que ça ! Voyez plutôt. »

❯ SELECT '2068-05-31'::date - '5 months'::interval;
+---------------------+
| ?column?            |
|---------------------|
| 2067-12-31 00:00:00 |
+---------------------+
SELECT 1
Time: 0.001s

❯ SELECT ('2068-05-31'::date - '4 months'::interval) - '1 months'::interval;
+---------------------+
| ?column?            |
|---------------------|
| 2067-12-31 00:00:00 |
+---------------------+
SELECT 1
Time: 0.002s

❯ SELECT ('2068-05-31'::date - '1 months'::interval) - '4 months'::interval;
+---------------------+
| ?column?            |
|---------------------|
| 2067-12-30 00:00:00 |
+---------------------+
SELECT 1
Time: 0.002s

❯ SELECT ('2068-05-31'::date - '3 months'::interval) - '2 months'::interval;
+---------------------+
| ?column?            |
|---------------------|
| 2067-12-29 00:00:00 |
+---------------------+
SELECT 1
Time: 0.002s

« Il apparait très clairement que les opérations arithmétique, même les plus triviales, ne sont ni associatives, ni commutatives.
— Mais on peut aisément éviter de tomber dans ce genre d’écueil ! se récria un jeune assistant.
— En effet, rétorqua Jérémie, la solution de contournement communément employée consiste, dans cette situation, à se baser initialement sur le premier jour du mois. Car vous l’aviez compris, l’écart constaté réside ici dans le fait que tous les mois ne se ressemblent pas : certains comptent 30 jours, d’autres 31, et même 28, voire 29 les années bissextiles, pour le mois de février. En s’efforçant à toujours avoir pour référence le premier jour du mois, nous retombons systématiquement sur nos pattes. »

Pianotant frénétiquement sur sa console, Jérémie en fit la démonstration sur le champ.

❯ SELECT
    make_date(extract(year from '2068-05-31'::date)::int,
              extract(month from '2068-05-31'::date)::int,
              1)
    - '3 months'::interval
    - '2 months'::interval
    + make_interval(0, 0, 0, extract(day from '2068-05-31'::date)::int)
    - '1 day'::interval;
+---------------------+
| ?column?            |
|---------------------|
| 2067-12-31 00:00:00 |
+---------------------+
SELECT 1
Time: 0.003s

« En passant, juste une petite digression, si vous me le permettez, pour insister sur l’usage d’intervalles, et notamment sur leur intérêt lorsqu’on souhaite restreindre à une période donnée le champ d’une requête. Il serait tentant, pour ce faire, de procéder de la sorte. »

❯ SELECT t.date
  FROM generate_series('2060-01-01'::date, '2070-01-01'::date, '1 month'::interval)
    AS t(date)
  WHERE t.date BETWEEN '2068-01-01'::date AND '2068-02-29'::date;
+------------------------+
| date                   |
|------------------------|
| 2068-01-01 00:00:00+00 |
| 2068-02-01 00:00:00+00 |
+------------------------+
SELECT 12
Time: 0.017s

« Cependant, l’emploi ici du mot-clé between nous force à considérer deux dates distinctes, dont une date de fin de période qui peut s’avérer difficile à calculer en amont. De plus, les bornes seront, elles, obligatoirement inclusives. Il nous est toutefois possible de ne manipuler qu’une seule date, la date de début de période, tout en bénéficiant d’une approche plus souple. Voici donc le même exemple traité à l’aide d’un intervalle. »

❯ SELECT t.date
  FROM generate_series('2060-01-01'::date, '2070-01-01'::date, '1 month'::interval)
    AS t(date)
  WHERE t.date >= '2068-01-01'::date
    AND t.date < '2068-01-01'::date + 2 * '1 month'::interval;
+------------------------+
| date                   |
|------------------------|
| 2068-01-01 00:00:00+00 |
| 2068-02-01 00:00:00+00 |
+------------------------+
SELECT 2
Time: 0.003s

« J’aurais pu éviter un peu d’arithmétiques en employant directement un intervalle de deux mois. Mais en procédant ainsi, il nous est plus aisé d’adapter la période étudiée. Fin de la parenthèse. Revenons à nos dates, dont nous n’avons là qu’effleuré le problème ! Mais restons cependant dans ce cadre simpliste et tâchons d’observer ce qu’il se passe lorsque nous manipulons des semaines, cette fois. Question : qu’est-ce qu’une semaine ?
— Une suite consécutive de sept jours commençant le lundi pour finir le dimanche, répondit l’assistant zélé.
— C’est exact. D’après la norme ISO 8601 du moins, car certains pays comme les États-Unis d’Amérique, le Canada ou encore l’Australie considèrent que la semaine commence le dimanche. Et combien l’année compte-t-elle de semaines ? 52, en effet. Parfois 53 ; 52 semaines de 7 jours ne totalisant que 364 jours. Ça se complique déjà un peu. »

Faisant du regard le tour de l’assemblée, Jérémie demanda :

« Si maintenant je vous posais la question suivante : Quand commence la première semaine de l’année ? Ou exprimé autrement, quel est le premier jour de la première semaine de l’année ? »

Tous semblaient réfléchir à cette question, subodorant une subtilité quelconque. Jérémie ne leur laissa pas le temps d’exposer le fruit de leur réflexion.

« Pour répondre à cette question, voyons ce que nous dit la norme ISO. »

Projetant un court extrait sur les terminaux personnels de ses auditeurs, l’on pouvait y lire :

By definition, ISO weeks start on Mondays and the first week of a year contains January 4 of that year.

« En d’autres termes, reprit-il, la première semaine de l’année est celle qui inclut le premier jeudi de ladite année. Prenons quelques exemples pour évaluer l’impact de ceci. »

❯ SELECT date::date,
         to_char(date, 'TMDay') AS jour,
         extract('isoyear' FROM date)::int AS "année ISO",
         extract('week' FROM date)::int AS semaine,
         extract('day' FROM (date + '2 month - 1 day'::interval))::int AS "fév.",
         extract('year' FROM date)::int AS année
  FROM generate_series('2060-01-01'::date,
                       '2070-01-01'::date,
                       '1 year'::interval) AS t(date);
+------------+----------+-------------+-----------+--------+---------+
| date       | jour     | année ISO   | semaine   | fév.   | année   |
|------------+----------+-------------+-----------+--------+---------|
| 2060-01-01 | Jeudi    | 2060        | 1         | 29     | 2060    |
| 2061-01-01 | Samedi   | 2060        | 53        | 28     | 2061    |
| 2062-01-01 | Dimanche | 2061        | 52        | 28     | 2062    |
| 2063-01-01 | Lundi    | 2063        | 1         | 28     | 2063    |
| 2064-01-01 | Mardi    | 2064        | 1         | 29     | 2064    |
| 2065-01-01 | Jeudi    | 2065        | 1         | 28     | 2065    |
| 2066-01-01 | Vendredi | 2065        | 53        | 28     | 2066    |
| 2067-01-01 | Samedi   | 2066        | 52        | 28     | 2067    |
| 2068-01-01 | Dimanche | 2067        | 52        | 29     | 2068    |
| 2069-01-01 | Mardi    | 2069        | 1         | 28     | 2069    |
| 2070-01-01 | Mercredi | 2070        | 1         | 28     | 2070    |
+------------+----------+-------------+-----------+--------+---------+
SELECT 11
Time: 0.006s

Après avoir bu une gorgée de l’eau contenue dans la bouteille posée près de lui, Jérémie expliqua :

« Si nous regardons l’année 2068 de plus près, nous constatons qu’elle débute un dimanche. Et, de ce fait, le premier jour de la première semaine de l’année 2068, si l’on s’en tient à la définition énoncée plus tôt, sera le lundi 2 janvier. Par chance, nos systèmes de gestion de base de données performent dans la manipulation de dates et nous offrent tout un panel de fonctions pour ce faire, comme vous pouvez le constater. Seulement il nous faut rester vigilant à chaque instant, et comprendre la portée et la signification des données que nous manipulons. »

Réajustant ses oculaires sur son nez aquilin, il poursuivit.

oculaires

« Mais trêve de mises en gardes à la portée de l’entendement d’un jeune étudiant de première année et passons, si vous le voulez bien, à la vitesse supérieure. »

À la bonne heure !

« Stocker et manipuler des dates est somme toute nécessaire mais rarement suffisant, argua-t-il. Ne serait-ce que pour convenir d’un rendez-vous ! En effet, dans ce cas il nous faut connaître précisément l’heure dudit rendez-vous, mais aussi son lieu. Et c’est là que les choses se compliquent, faisant survenir de nouvelles notions telles que les fuseaux horaires — les fameuses time zones — ainsi que les changements d’heure, qu’ils soient récurrents — comme la DST — ou ponctuels. Et si l’on n’y prend pas gare, on peut rapidement se retrouver dans des situations inextricables. »

Jouant de son clavier ergoprédictif avec toute l’aisance due à une longue habitude, Jérémie projeta de nouvelles données à son attentif auditoire.

❯ BEGIN;

  DROP TABLE IF EXISTS tempo;

  CREATE TABLE tempo(ts timestamp, tstz timestamptz);

  SET TIMEZONE TO 'Europe/Paris';
  SELECT now();
  INSERT INTO tempo VALUES(now(), now());

  SET TIMEZONE TO 'Asia/Pyongyang';
  SELECT now();
  INSERT INTO tempo VALUES(now(), now());

  SET TIMEZONE TO 'Europe/Paris';
  TABLE tempo;

  SET TIMEZONE TO 'Asia/Seoul';
  TABLE tempo;

  COMMIT;

BEGIN
DROP TABLE
CREATE TABLE
SET
+-------------------------------+
| now                           |
|-------------------------------|
| 2068-05-31 15:48:04.505633+02 |
+-------------------------------+
SELECT 1
INSERT 0 1
SET
+----------------------------------+
| now                              |
|----------------------------------|
| 2068-05-31 22:18:04.505633+08:30 |
+----------------------------------+
SELECT 1
INSERT 0 1
SET
+----------------------------+-------------------------------+
| ts                         | tstz                          |
|----------------------------+-------------------------------|
| 2068-05-31 15:48:04.505633 | 2068-05-31 15:48:04.505633+02 |
| 2068-05-31 22:18:04.505633 | 2068-05-31 15:48:04.505633+02 |
+----------------------------+-------------------------------+
SELECT 2
SET
+----------------------------+-------------------------------+
| ts                         | tstz                          |
|----------------------------+-------------------------------|
| 2068-05-31 15:48:04.505633 | 2068-05-31 22:48:04.505633+09 |
| 2068-05-31 22:18:04.505633 | 2068-05-31 22:48:04.505633+09 |
+----------------------------+-------------------------------+
SELECT 2
COMMIT
Time: 0.017s

« Notez l’impact du fuseau horaire ! Pour l’exemple, j’ai ici demandé au système de gestion de bases de données de simuler différentes localisations avant de persister l’horodatage courant. Les plus observateurs parmi vous auront remarqué qu’au sein d’une transaction, le temps est figé. Vous vous serez également aperçus qu’en tout dernier lieu j’ai consulté ma donnée depuis Séoul, alors qu’elle a été enregistrée depuis un fuseau horaire aujourd’hui désuet, — et ce depuis 50 ans, très exactement — celui de Pyongyang, ancienne capitale de ce que l’on appelait autrefois la Corée du Nord, aujourd’hui simple préfecture de la République Réunifiée de Corée… mais je m’égare. L’emploi de ce fuseau horaire me permet de mettre en évidence un fait souvent négligé : un décalage entre deux fuseaux horaires ne se mesure pas nécessairement en heures pleines. La preuve en est, nous avons ici un décalage positif de huit heures et trente minutes par rapport au temps universel terrestre. »

une main se leva

Une main se leva dans l’assistance, et son propriétaire prit la parole :

« Pardonnez-moi de vous interrompre, et corrigez-moi si je me trompe, mais j’ai le sentiment que ce que l’on gagne d’un côté, on le perd de l’autre. L’horodatage prenant en compte le fuseau horaire nous présentant l’information du point de vue de l’observateur, toute trace du fuseau horaire d’origine semble avoir disparue. Y a-t-il un quelconque moyen de retrouver cette donnée ?
— Ceci est finement observé, rétorqua Jérémie. Et le seul moyen de garder une trace de ce fuseau horaire — plus précisément du décalage par rapport à UTC — est de stocker ce décalage dans une colonne à part entière, comme ceci. »

❯ BEGIN;

  SET TIMEZONE TO 'Europe/Paris';
  SELECT extract(timezone FROM now());

  commit;

BEGIN
SET
+-------------+
| date_part   |
|-------------|
| 7200.0      |
+-------------+
SELECT 1
COMMIT
Time: 0.002s

« Ainsi, vous l’aurez deviné, l’unité utilisée pour définir ce décalage est la seconde. Je vous remercie, cher collègue, pour cette interruption fort à propos qui me donne l’occasion de rebondir sur la question de l’utilisation de cette donnée une fois recueillie qui, je le vois, brûle les lèvres de plusieurs de nos confrères. »

Un murmure d’approbation parcouru brièvement la salle.

« Rien de plus simple, je vous assure ! En effet, le décalage étant fonction du fuseau horaire universel, il suffit de s’y positionner pour référence et d’appliquer notre décalage sous la forme d’un intervalle de temps en secondes. Regardez. »

Et instantanément furent projetées de nouvelles lignes de code illustrant ses dires.

❯ BEGIN;

  SET TIMEZONE TO 'UTC';
  SELECT now() + 7200 * '1 second'::interval;

  COMMIT;

BEGIN
SET
+----------------------------+
| ?column?                   |
|----------------------------|
| 2068-05-31 15:48:04.505633 |
+----------------------------+
SELECT 1
Time: 0.003s

« Dès lors, vous en conviendrez, en appliquant avec un tant soit peu de rigueur quelques notions élémentaires, et en anticipant les éventuels aléas que nous réserve la géopolitique et que nous enseigne la scientonomie, nombre d’erreurs et de situations baroques pourraient être évitées. »

Subjuguée par la perspicacité de cette démonstration, la foule assemblée, claquant des doigts, acclama Jérémie qui se sentit aussitôt transporté par une vague de bien-être onirique. La cacophonie de cette multitude se fondit, claquant à l’unisson, pour ne devenir qu’un…

snap ! snap !

snap snap

« Jérémie ? Jérémie ! Ma parole, mon garçon, voilà que vous rêvassez encore ! Gaston lui-même ferait figure d’employé zélé à vos côtés ! Je ne sais pas à quoi vous occupez vos nuits, mais tâchez de leur réserver vos rêveries et d’être davantage attentif, bon sang !
— Pardonnez-moi, Professeur, marmonna Jérémie sorti de sa stupeur.
— Passons. Le 30 mai, disais-je, reprit le Professeur, c’est tout bonnement impossible, c’est le jour du mariage de ma fille !
— Bien sûr. Toutes mes excuses, Professeur. Je vais immédiatement vous trouver un autre rendez-vous.

Sources