Discussion :

[anthomicro]

Bonjour à tous

Une fois n'est pas coutume, je rencontre un souci de requête SQL sur des données un peu particulières.

Je dispose de véhicules qui régulièrement sont positionnés à une certaine date via une table "vehicules_positions". On prend en fait un instantané de la position (il n'y a donc pas de date de fin). La structure est la suivante :



Dans mes informations (champ "informations") qui sont stockées en JSON, je récupère l'endroit où se trouve le véhicule :

{"endroit":"A", "kilometres":12345} par exemple.

Je souhaite pouvoir faire cela :

Chaque jour d'un mois particulier (prenons décembre 2014 par exemple) je souhaite pouvoir dire si le véhicule se trouve à l'endroit A, et ne le récupérer que dans ce cas, sachant qu'on considère que le véhicule n'a pas bougé tant qu'une nouvelle prise de position n'est pas disponible.

Pour récupérer l'endroit où est positionné le véhicule à une date donnée (dans ce cas le 31/12/2014 à 23:59:59), je procède comme ceci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
WITH maxposition AS(
	SELECT vehicule, MAX(id) AS id_max 
	FROM vehicules_positions 
	WHERE vehicule=2479 AND date < 1420066799  //Cette date correspond au 31/12/2014 à  23:59:59
	GROUP BY vehicule
) 
 
SELECT mp.vehicule, mp.id_max, trim(both '"' from (p.informations::json->'endroit')::text) AS endroit 
	FROM maxposition mp 
	LEFT JOIN vehicules_positions p ON mp.id_max=p.id

Mon problème étant de pouvoir faire cela dynamiquement pour chaque jour du mois, en étant le plus performant possible (j'ai une quantité assez importante de positions, plusieurs millions d'enregistrements).

Je génère donc une série listant tous mes jours du mois :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT (generate_series('2014-12-01', '2014-12-31', '1 day'::interval))::date AS date_du_jour

Je sèche maintenant complètement pour joindre mes positions maximales pour chaque jour du mois.

Auriez-vous la gentillesse de m'aider SVP ?

Merci

[Waldar]

Votre problème c'est de convertir votre timestamp unix en postgresql.
Et bien à la timezone près, c'est natif :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT to_timestamp(1420066799);

[anthomicro]

Bonjour,

Mon problème était plus un souci de groupement plutôt que de timestamp. Je l'ai résolu en ajoutant une table de positions qui, à chaque mouvement, enregistre la position du véhicule. Cela m'évite de la recalculer à chaque fois