Utiliser des dates/heures haute précision dans SQLite : sqlean-time

 (antonz.org)
2 points par GN⁺ 2024-08-16 | 1 commentaires | Partager sur WhatsApp
  • Quand les fonctions de date natives de SQLite ne suffisent pas, sqlean-time ajoute via une extension des types Time et Duration ainsi que des fonctions de date/heure avec une précision à la nanoseconde
  • Une valeur Time est composée des secondes écoulées depuis 0001-01-01 00:00:00 UTC et des nanosecondes dans la seconde courante ; stockée sous forme de BLOB de 13 octets, elle peut couvrir des plages de plusieurs milliards d’années dans le passé comme dans le futur
  • Un stockage en NUMBER 64 bits basé sur l’époque Unix est aussi possible, mais plus l’unité est fine, plus la plage se réduit ; à la nanoseconde, seules les années 1678 à 2262 peuvent être représentées
  • L’API couvre la création, l’extraction de champs, la conversion vers/depuis le temps Unix, la comparaison, l’arithmétique, la troncature et l’arrondi, ainsi que le formatage et le parsing ISO 8601 ; les valeurs sont toujours stockées et calculées en UTC
  • Les calculs calendaires supposent le calendrier grégorien et n’incluent pas les secondes intercalaires ; pour les calculs en jours, mois et années, il faut donc utiliser time_add_date() plutôt que time_add() basé sur Duration

Le modèle temporel de sqlean-time

  • sqlean-time est une extension qui ajoute à SQLite une gestion de date/heure haute précision
  • Une extension SQLite peut être ajoutée en téléchargeant un fichier puis en exécutant une seule commande dans la base de données
  • L’extension fonctionne autour de deux types de valeurs
    • Time : un instant précis
    • Duration : une durée

Représentation de Time et plage de stockage

  • Time est constitué d’une paire (seconds, nanoseconds)
    • seconds : un entier 64 bits représentant le nombre de secondes écoulées depuis le temps zéro 0001-01-01 00:00:00 UTC
    • nanoseconds : la valeur de nanosecondes dans la seconde courante, dans l’intervalle 0-999999999
  • Si l’on a besoin d’un maximum de souplesse, une valeur Time peut être stockée sous sa représentation interne en BLOB de 13 octets
    • Cette méthode permet de représenter avec une précision à la nanoseconde des dates situées à plusieurs milliards d’années dans le passé comme dans le futur
  • Le stockage est aussi pris en charge sous forme de NUMBER entier 64 bits exprimant les secondes, millisecondes, microsecondes ou nanosecondes écoulées depuis l’époque Unix 1970-01-01 00:00:00 UTC
    • secondes : précision à la seconde sur plusieurs milliards d’années dans le passé et le futur
    • millisecondes : précision à la milliseconde sur 292 millions d’années autour de 1970
    • microsecondes : de l’année -290307 à 294246
    • nanosecondes : de l’année 1678 à 2262
  • Time est toujours stocké et manipulé en UTC
    • Une conversion vers/depuis un décalage de fuseau horaire spécifique reste possible
  • Les calculs calendaires supposent toujours le calendrier grégorien
    • Les secondes intercalaires ne sont pas utilisées

Duration et création de valeurs

  • Duration est un entier 64 bits en nanosecondes
    • Il permet de représenter des durées allant jusqu’à environ 290 ans
    • Il peut être stocké comme NUMBER
  • L’instant courant peut être obtenu avec time_now()
    • Exemple : time_fmt_iso(time_now()) renvoie une chaîne ISO comme 2024-08-06T21:22:15.431295000Z
  • Une date/heure précise peut être créée avec time_date()
    • Si seule la date est fournie, l’heure est minuit UTC
    • Il est possible de fournir aussi l’heure, les minutes, les secondes et les nanosecondes
    • Si un décalage de fuseau horaire est passé, la valeur est convertie en instant UTC

Extraction des champs de date/heure

  • Les fonctions d’extraction de champs individuels renvoient l’année, le mois, le jour, l’heure, la minute, la seconde, les nanosecondes, le jour de la semaine, le jour dans l’année, l’année ISO et la semaine ISO
    • Exemples : time_get_year(), time_get_month(), time_get_day(), time_get_hour(), time_get_minute(), time_get_second(), time_get_nano()
  • La fonction générique time_get() extrait une valeur à partir d’un nom de champ sous forme de chaîne
    • Exemples pris en charge : millennium, century, decade, year, quarter, month, day
    • Unités de temps : hour, minute, second, milli, micro, nano
    • Exemples liés à l’ISO et au calendrier : isoyear, isoweek, isodow, yearday, weekday
    • La valeur d’époque Unix peut être récupérée avec epoch

Conversion du temps Unix

  • Des fonctions sont fournies pour créer une valeur Time à partir du temps Unix
    • time_unix(seconds)
    • time_unix(seconds, nanoseconds)
    • time_milli(milliseconds)
    • time_micro(microseconds)
    • time_nano(nanoseconds)
  • Il existe aussi des fonctions pour reconvertir une valeur Time en temps Unix
    • time_to_unix()
    • time_to_milli()
    • time_to_micro()
    • time_to_nano()
  • Les systèmes d’exploitation de type Unix enregistrent souvent le temps sous forme de secondes sur 32 bits, mais time_to_unix() renvoie une valeur 64 bits
    • Valable sur une plage de plusieurs milliards d’années dans le passé comme dans le futur
    • time_to_milli() couvre jusqu’à 292 millions d’années autour de 1970
    • time_to_micro() couvre de l’année -290307 à 294246
    • time_to_nano() couvre de l’année 1678 à 2262

Comparaison et arithmétique

  • Les fonctions de comparaison déterminent l’ordre de deux valeurs Time
    • time_after() : renvoie si le premier instant est postérieur au second
    • time_before() : renvoie si le premier instant est antérieur au second
    • time_compare() : renvoie 1 si c’est après, -1 si c’est avant, 0 si c’est identique
    • time_equal() : renvoie si les deux valeurs représentent le même instant
  • time_add() ajoute une Duration à une valeur Time
    • Une Duration négative permet de soustraire
    • Elle peut être utilisée avec des constantes Duration comme dur_us(), dur_ms(), dur_s(), dur_m(), dur_h()
  • Pour ajouter des jours, mois ou années, il faut utiliser time_add_date() et non time_add()
    • time_add_date() ajoute des années, des mois et des jours, et peut soustraire avec des valeurs négatives
  • time_sub() renvoie la durée entre deux valeurs Time en nanosecondes
  • time_since() renvoie le temps écoulé depuis l’instant indiqué, en nanosecondes
  • time_until() renvoie la durée restante jusqu’à l’instant indiqué, en nanosecondes

Troncature et arrondi

  • time_trunc() tronque une valeur Time à la précision du champ indiqué
    • Exemples pris en charge : millennium, century, decade, year, quarter, month, week, day, hour, minute, second, milli, micro
    • Par exemple, tronquer 2011-11-18T15:56:35.666777888Z à hour donne 2011-11-18T15:00:00Z
  • Il est aussi possible de tronquer à un multiple d’une Duration donnée
    • Exemples : 12*dur_h(), dur_h(), 30*dur_m(), dur_m(), 30*dur_s(), dur_s()
  • time_round() arrondit au multiple le plus proche de la Duration indiquée
    • Par exemple, arrondir 2011-11-18T15:56:35.666777888Z avec dur_h() donne 2011-11-18T16:00:00Z
    • Arrondir la même valeur avec dur_s() donne 2011-11-18T15:56:36Z

Formatage et parsing

  • time_fmt_iso() renvoie une valeur Time sous forme de chaîne ISO 8601
    • Il peut recevoir en option un décalage de fuseau horaire, effectuer la conversion vers cet offset puis formater le résultat
    • Une valeur avec nanosecondes s’écrit par exemple 2011-11-18T15:56:35.666777888Z
    • Avec un offset, elle s’écrit par exemple 2011-11-18T18:56:35.666777888+03:00
  • time_fmt_datetime(), time_fmt_date(), time_fmt_time() renvoient respectivement des chaînes datetime, date et time
    • Un décalage de fuseau horaire optionnel peut être fourni
  • time_parse() parse une chaîne formatée en valeur Time
    • Chaîne ISO 8601 avec nanosecondes et fuseau horaire
    • Chaîne ISO 8601 avec nanosecondes et Z UTC
    • Chaîne ISO 8601 avec fuseau horaire
    • Chaîne ISO 8601 UTC
    • Date/heure UTC au format YYYY-MM-DD HH:MM:SS
    • Date UTC au format YYYY-MM-DD
    • Heure UTC au format HH:MM:SS
  • Les layouts pris en charge par time_parse() forment un ensemble limité

Constantes Duration

  • Des fonctions sont fournies pour renvoyer des durées courantes en nanosecondes
    • dur_ns() → 1
    • dur_us() → 1000
    • dur_ms() → 1000000
    • dur_s() → 1000000000
    • dur_m() → 60000000000
    • dur_h() → 3600000000000

Base d’implémentation et installation

  • L’extension est implémentée en C, mais sa conception et son implémentation reposent largement sur le package time de la bibliothèque standard Go
    • Ce package est sous licence BSD 3-Clause License
  • L’installation consiste à télécharger la dernière release puis à charger l’extension dans la CLI SQLite
    • Exemple : .load ./time
    • Après chargement, on peut exécuter des requêtes comme select time_now();

À lire aussi

1 commentaires

 
GN⁺ 2024-08-16
Commentaires sur Hacker News

  • Je me demande si cela gère aussi les cas particuliers comme les changements de fuseau horaire et les discontinuités de l’heure locale, que Jon Skeet avait célèbrément résumés
    https://stackoverflow.com/questions/6841333/why-is-subtracti...
    Computerphile l’explique aussi très bien dans une vidéo de 10 minutes
    https://www.youtube.com/watch?v=-5wpm-gesOY
    J’ai appris il y a longtemps à ne pas écrire moi-même de bibliothèques de date/heure ou de cryptographie. Il existe une infinité de cas limites qui peuvent vous mordre très fort, donc je regarde ce genre de nouvelle bibliothèque avec scepticisme

    • Cette bibliothèque ne traite pas du tout la notion d’heure locale. Tout est basé sur l’UTC, et l’utilisateur peut fournir un décalage de fuseau horaire, mais la partie difficile — calculer ce décalage — incombe à l’appelant
      La documentation pourrait être un peu plus claire. L’auteur parle de “time zones”, mais la bibliothèque ne gère en réalité que les décalages de fuseau horaire. Un fuseau horaire, c’est quelque chose comme America/New_York ; un décalage de fuseau horaire, c’est la différence avec l’UTC. New York est à -14400 secondes aujourd’hui, mais sera à -18000 secondes dans quelques mois à cause du changement d’heure d’été

  • Les trois représentations/tailles du temps différentes sont intéressantes. Par exemple, je ne vois pas quel cas d’usage nécessiterait une précision à la nanoseconde sur une plage de plusieurs milliards d’années
    Ce qui prête encore plus à confusion, c’est que la granularité temporelle est extrêmement fine, alors que la précision à la nanoseconde pour les durées ne couvre qu’une plage de ±290 ans

    • Une fois qu’on a décidé d’utiliser une précision à la nanoseconde, une représentation 64 bits ne permet de couvrir que 584 ans, et ce n’est pas suffisant. Il faut au moins 2 bits de plus pour représenter l’année 2024
      Mais si l’on ajoute 2 bits, il n’y a pas vraiment de raison de ne pas en ajouter 16 ou 32. On couvre alors tout le monde, de ceux qui calculent le temps que met la lumière à parcourir 30 cm à ceux qui calculent l’âge de l’univers
      J’imagine que la décision de conception a probablement suivi ce genre de raisonnement :)
      Bien sûr, il est difficile de fournir une exactitude sous la seconde sans prise en charge des secondes intercalaires, et on voit mal ce que signifierait la prise en charge des secondes intercalaires avant la civilisation humaine
    • Cette approche a très bien fonctionné pour moi et pour des milliers d’autres développeurs Go. C’est pourquoi j’ai choisi cette approche

  • Digression connexe : les bases de données devraient suivre les unités. Si l’on a une colonne de temps, on devrait pouvoir la déclarer, par exemple, comme une durée en secondes float64
    On pourrait alors écrire SELECT * FROM my_table WHERE duration_s >= 2h, et la base de données convertirait automatiquement “2h” en 7200.0 secondes pour comparer des unités identiques pendant le scan de la table
    Il y a quelques années, j’ai créé une base de données SQL spécialisée avec ce type de gestion native des unités, mais je n’en ai pas vu avant ni depuis, et cela ressemble à un manque dans l’écosystème des interfaces
    Cela ne devrait pas se limiter au temps. Il faudrait pouvoir gérer toute la liste des unités : masse, volume, quantité d’information, température, etc. La base de données pourrait aussi refuser des expressions mathématiquement absurdes comme SELECT 2h + 15kg -- type error!
    Cela aiderait beaucoup à détecter tôt les erreurs d’analyse

  • Je pense qu’il est important de préciser si des entiers signés sont utilisés ou non. À la lecture de la documentation, on dirait qu’ils le sont, mais ce n’est pas totalement clair
    S’il s’agit d’entiers signés, il peut y avoir plusieurs chaînes de bits représentant la même date et heure, et ce n’est pas souhaitable

    • C’est clairement signé. Il est indiqué : “pour soustraire, utilisez une durée négative”
      Cela dit, le motif de bits est une affaire interne à la bibliothèque. Si vous trouvez un bug dans le code, signalez-le évidemment, et proposez un correctif si possible
    • Comment peut-il y avoir “plusieurs chaînes de bits représentant la même date et heure” ?

  • J’aimerais vraiment que SQLite3 dispose d’un système de types extensible

    • Pour avoir un peu contribué à PostgreSQL : non, il ne faut pas faire ça !!!!
      Un système de types extensible est catastrophique pour les performances côté utilisateur final d’une base de données. On ne peut alors rien court-circuiter lors du parsing et de l’optimisation des requêtes. Il faut sans cesse consulter les catalogues système de requêtes pour vérifier le type de chaque opérande, trouver la bonne implémentation d’opérateur, la bonne famille/classe d’opérateurs d’index, etc.
      L’entrée/sortie des valeurs passe aussi par des fonctions stockées dans le catalogue système. Même select 1 ne peut pas être résolu sans consulter le catalogue système
      Il faut un ensemble approprié de types intégrés et des mécanismes de composition comme les structs/JSON. C’est ainsi que fonctionnent la plupart des bases de données, à l’exception de PostgreSQL, et je suis fermement convaincu que c’est la bonne direction

  • Question un peu paresseuse façon Ask HN : d’après votre expérience, qu’est-ce qui est le plus utile ou précieux ? La représentation à la nanoseconde, ou bien la représentation d’années hors de la plage nanoseconde, comme 1678–2200 ?
    Je ne fais pas de vrais travaux scientifiques, donc la valeur de la nanoseconde me semble limitée à des expériences très ingénieuses ou au suivi de transactions financières, où la plage est plus restreinte
    En revanche, la capacité à représenter des dates historiques me semble devoir être plus souvent nécessaire. Qu’en pensez-vous ?

    • Les dates historiques sont clairement plus importantes
      Rien qu’en réduisant la précision à 10 nanosecondes, on obtient une plage suffisante en pratique
    • C’est un peu comme demander ce qui est le plus utile entre un marteau et un tournevis. Cela dépend du travail à faire

  • Je me demande pourquoi ne pas utiliser le style Go, avec un timestamp Unix en nanosecondes sous forme d’int64 signé. Cela ne couvrirait pas des millions d’années avec une précision à la nanoseconde, mais est-ce vraiment nécessaire ?

    • Avec cette précision et cette taille, on ne peut couvrir que la période de 1678 à 2262, ce qui limite fortement la capacité à représenter des dates et heures historiques
    • Stocker un timestamp Unix en nanosecondes n’est pas le style Go, mais on peut le faire avec cette extension
      select time_to_nano(time_now());
      -- 1722979335431295000

  • J’aimerais que des expressions comme “secondes depuis l’epoch” ne soient utilisées que lorsqu’elles signifient exactement cela
    Je me demande ce que renverra select time_sub(time_date(2011, 11, 19), time_date(1311, 11, 18));

    • Pourquoi le souhaitez-vous ?
      Quelques raisons plausibles me viennent à l’esprit, mais la seule chose vraiment importante est : “quelle epoch ?” Dans les systèmes basés sur UNIX, ou qui cherchent à en imiter le comportement, c’est bien défini. Mais comme vous n’avez pas dit quelle était votre objection, il est difficile de réfuter ou de justifier l’état actuel des choses
      time_date(1311, 11, 18) n’est pas défini pour l’epoch utilisée par la plupart des systèmes informatiques, donc n’importe quel résultat est possible. MAX_INT, MIN_INT, 0, une valeur plausible mais ne tenant pas compte des réformes du calendrier, une valeur convertie vers une autre epoch pour calculer le nombre exact de secondes, etc. On pourrait même soutenir qu’avant GMT/UTC, tout était en heure locale, donc qu’il n’existe pas d’epoch valide
      Bien sûr, on peut défendre les deux positions quant à la prise en charge des valeurs négatives. On peut s’attendre à ce que le moment exactement 24 heures avant le 1970-1-1 0:00:00 UTC soit -86400, mais “since” suggère fortement des valeurs positives uniquement
      D’autres personnes peuvent aussi avoir une epoch complètement différente pour d’autres raisons, et si tout le monde est d’accord dans le domaine d’utilisation, cela peut très bien convenir
      Ou aviez-vous une autre raison de vous y opposer ?
    • Il est indiqué que “si le résultat dépasse la valeur maximale pouvant être stockée dans Duration, la durée maximale est renvoyée”