HN en clair : fonctionnalités de date/heure haute précision dans SQLite

 (antonz.org)
2 points par GN⁺ 2024-08-16 | 1 commentaires | Partager sur WhatsApp

Extension de date/heure haute précision

SQLite fournit des fonctions de date de base, mais comme davantage de fonctionnalités étaient nécessaires, une extension de date/heure haute précision appelée sqlean-time a été créée. Cette extension propose une API structurée et diverses fonctionnalités.

Remarque. Ajouter une extension à SQLite est très simple. Il suffit de télécharger le fichier et d’exécuter une seule commande de base de données.

Concepts

Cette extension utilise deux types de valeurs : le temps (Time) et la durée (Duration).

  • Temps (Time) : une paire composée de secondes et de nanosecondes, représentant les secondes écoulées depuis l’instant zéro (0001-01-01 00:00:00 UTC) ainsi que les nanosecondes à l’intérieur de la seconde courante.

    • Il est possible de stocker le temps dans cette représentation interne, ce qui permet de représenter des dates situées à plusieurs milliards d’années dans le passé ou le futur avec une précision à la nanoseconde.
    • Le temps peut aussi être stocké comme des secondes depuis l’époque Unix (1970-01-01 00:00:00 UTC), en millisecondes, microsecondes ou nanosecondes.
    • Le temps est toujours stocké et manipulé en UTC, mais peut être converti avec un décalage de fuseau horaire spécifique.

  • Durée (Duration) : un entier 64 bits en nanosecondes, capable de représenter des valeurs allant jusqu’à environ 290 ans.

Création de valeurs temporelles

  • Heure actuelle :

    select time_fmt_iso(time_now());  -- 2024-08-06T21:22:15.431295000Z

  • Date/heure spécifique :

    select time_fmt_iso(time_date(2011, 11, 18));  -- 2011-11-18T00:00:00Z
select time_fmt_iso(time_date(2011, 11, 18, 15, 56, 35));  -- 2011-11-18T15:56:35Z

Extraction de champs temporels

Il existe des fonctions pour extraire divers champs de date/heure :

select 'year  = ' || time_get_year(time_now());
select 'month  = ' || time_get_month(time_now());
select 'day   = ' || time_get_day(time_now());

Temps Unix

Fonctions pour créer des valeurs temporelles à partir du temps Unix (temps écoulé depuis 1970-01-01 UTC) :

select time_fmt_iso(time_unix(1321631795));  -- 2011-11-18T15:56:35Z

Fonctions pour convertir une valeur temporelle en temps Unix :

select time_to_unix(time_now());  -- 1722979335

Comparaison temporelle

Fonctions pour comparer des valeurs temporelles :

select time_after(time_now(), time_date(2011, 11, 18));  -- 1
select time_before(time_now(), time_date(2011, 11, 18));  -- 0

Calculs temporels

Fonctions pour ajouter une durée à une valeur temporelle :

select time_fmt_iso(time_add(time_now(), 24*dur_h()));  -- 2024-08-07T21:22:15.431295000Z

Constantes de durée :

  • dur_us() - 1 microseconde
  • dur_ms() - 1 milliseconde
  • dur_s() - 1 seconde
  • dur_m() - 1 minute
  • dur_h() - 1 heure

Arrondi

Fonctions pour arrondir des valeurs temporelles à la précision du champ spécifié :

select 'original  = ' || time_fmt_iso(t.v) from t union all
select 'millennium = ' || time_fmt_iso(time_trunc(t.v, 'millennium')) from t;

Formatage

Fonctions qui renvoient des chaînes temporelles au format ISO 8601 :

select time_fmt_iso(time_date(2011, 11, 18, 15, 56, 35, 666777888), 3*3600);  -- 2011-11-18T18:56:35.666777888+03:00

Constantes de durée

Fonctions qui renvoient des durées courantes en nanosecondes :

select dur_ns();  -- 1
select dur_us();  -- 1000

Remerciements

Cette extension est implémentée en C, et sa conception comme son implémentation s’appuient sur le package time de la bibliothèque standard de Go (licence BSD 3-Clause).

Installation et utilisation

  1. Télécharger la dernière version
  2. Utilisation dans l’interface en ligne de commande SQLite : 
    sqlite> .load ./time
sqlite> select time_now();

Récapitulatif GN⁺

  • L’extension sqlean-time ajoute à SQLite des fonctionnalités de date/heure haute précision permettant diverses opérations temporelles.
  • Le temps et les durées peuvent être traités à la nanoseconde, ce qui permet des calculs temporels extrêmement précis.
  • Elle fournit diverses fonctions de formatage et de comparaison du temps, faciles à utiliser pour les développeurs.
  • Elle offre bien plus de possibilités que les fonctions de date de base de SQLite, ce qui la rend utile pour les projets nécessitant des opérations temporelles complexes.

À lire aussi

1 commentaires

 
GN⁺ 2024-08-16
Commentaires sur Hacker News

  • Question sur le fait de savoir si cela gère les cas particuliers des changements de fuseau horaire et des discontinuités de l’heure locale documentés par Jon Skeet

    • Lien connexe : Stack Overflow
    • Lien vers l’explication vidéo de 10 minutes de Computerphile : YouTube

  • Il vaut mieux ne pas développer soi-même des bibliothèques de date/heure et de chiffrement

    • Des cas limites sans fin peuvent poser problème
    • Raison pour laquelle on reste sceptique face à une nouvelle bibliothèque

  • Les trois différentes représentations/échelles du temps sont intéressantes

    • On se demande quels cas d’usage nécessitent une précision à la nanoseconde sur des périodes de plusieurs milliards d’années
    • Il est déroutant de n’offrir qu’une plage de ±290 ans avec une précision à la nanoseconde

  • Il est important de préciser si des entiers signés sont utilisés ou non

    • À la lecture de la documentation, ce n’est pas clair : cela peut être des entiers signés ou non
    • S’il s’agit d’entiers signés, plusieurs chaînes de bits peuvent représenter la même date et heure

  • Il serait bien que SQLite3 dispose d’un système de types extensible

  • C’est jugé très cool, tout en soulignant une fonctionnalité importante manquante dans SQLite

  • Il est soutenu que la base de données devrait suivre les unités

    • Par exemple, il devrait être possible d’indiquer qu’une colonne temporelle représente des secondes en float64
    • La base de données devrait pouvoir convertir "2h" en 7200.0 secondes et comparer cela lors d’un parcours complet de table
    • Par le passé, quelqu’un a écrit une base de données SQL spécialisée gérant ce type d’unités, mais n’en a plus revu depuis
    • Cela devrait gérer non seulement le temps, mais aussi toutes les unités : masse, volume, information, température, etc.
    • On pourrait apprendre à la base de données à rejeter les opérations mathématiquement dénuées de sens afin de détecter plus tôt les erreurs mathématiques

  • Question sur ce qui est le plus utile entre la représentation en nanosecondes et les années en dehors de cette plage en nanosecondes

    • Comme il ne s’agit pas de science « exacte », la valeur des nanosecondes est limitée
    • Il semble plus fréquent d’avoir besoin de représenter des dates historiques

  • Proposition d’utiliser un timestamp Unix à la manière de Golang, en nanosecondes, avec un int64 signé

    • Cela ne couvrira peut-être pas des millions d’années avec une précision à la nanoseconde, mais on peut se demander si c’est vraiment nécessaire

  • Il est affirmé qu’il ne faut pas utiliser l’expression « secondes depuis l’epoch » à moins de vouloir exactement dire cela

    • Exemple de requête : select time_sub(time_date(2011, 11, 19), time_date(1311, 11, 18));