Forums PostgreSQL.fr

TitanGIS

Membre

Requêtage d'un timestamp pour suivre le remplissage d'une table

Bonjour à toutes et à tous.

J’espère ne pas me planter de section du forum.

Je rencontre une petite difficulté quant à l'utilisation du SQL et je ne trouve pas de solution à mon problème.
J'ai à ma disposition une table qui se remplit quotidiennement de quelques milliers de lignes, en provenance d'OpenStreetMap. La donnée est reçue quotidiennement, sur un territoire suffisamment grand pour qu'il y ait au moins 1 entrée par jour -sauf interruption de service du côté OSM- et reçue avec un timestamp qui trace les modifications.

Afin de suivre l'évolution de cette table, et son bon remplissage, j'utilise cette petite requête rapide pour vérifier que tout est en ordre et que chaque jour, tout c'est bien déroulé :

SELECT count(*), date_trunc('day', tstamp)
FROM osm_afr2.ways
WHERE tstamp > '2018-06-15'  ###pour vérifier si tout est en ordre depuis le 15 juin, et que j'ai pas raté un jour !
GROUP BY date_trunc('day', tstamp)
ORDER BY date_trunc('day', tstamp);

Me retourne :

count(*);date_trunc;
20563;"2018-06-15 00:00:00"
13839;"2018-06-16 00:00:00"
...
20832;"2018-06-29 00:00:00"
12481;"2018-07-01 00:00:00"
2555;"2018-07-02 00:00:00"

Si, sur les 15 derniers jours, il est facile de voir qu'il manque un jour (le 30 juin), et donc que le processus de mise à jour de la base diff est planté, l'opération est beaucoup plus délicate sur une année entière ou plus.
Ainsi, est-il possible de retourner un résultat de type :

count(*);date_trunc;
20563;"2018-06-15 00:00:00"
13839;"2018-06-16 00:00:00"
...
20832;"2018-06-29 00:00:00"
NULL;"2018-06-30 00:00:00"
12481;"2018-07-01 00:00:00"
2555;"2018-07-02 00:00:00"

Merci à vous par avance pour vos éclairages !

Dernière modification par TitanGIS (02/07/2018 17:12:58)

rjuju

Administrateur

Re : Requêtage d'un timestamp pour suivre le remplissage d'une table

Bonjour,

Cette requête devrait vous donner la liste des jours n'ayant pas d'enregistrement sur la dernière année :

SELECT t.tstamp::date
FROM osm_afr2.ways
right join (
    select tstamp
    from generate_series(current_date - interval '1 year', current_date, '1 day') tstamp
) t on ways.tstamp::date = t.tstamp::date
GROUP BY t.tstamp::date
having count(ways.tstamp) = 0
ORDER BY t.tstamp::date;

---

Julien.
https://rjuju.github.io/

TitanGIS

Membre

Re : Requêtage d'un timestamp pour suivre le remplissage d'une table

Merci beaucoup Julien, ça colle parfaitement à mon besoin.

Merci pour votre aide.