Discussion :

[Totorote]

Bonjour,

J'ai une table qui de LOG qui contient le nom de fichier et la date au format TIMESTAMP à laquelle on reçoit le fichier.

Je voudrais calculer l'heure moyenne à laquelle on reçoit les fichiers.

Exemple de données :

NOM_FICHIER ----------------- FILE_RECEP_FIC
ADRESSE ----------------- 10/05/2018/ 09:06:00
ADRESSE ---------------- 28/08/2018 04:01:00
ADRESSE --------------- 06/08/2018 04:01:00
ADRESSE ---------------- 05/04/2018 09:27:00
ADRESSE --------------- 21/08/2018 04:03:00
ADRESSE --------------- 09/06/2018 10:18:00
ADRESSE --------------- 21/08/2018 04:03:00

Merci d'avance pour votre retour

[Totorote]

SOLUTION TROUVEE :

Select
NOM_COLONNE,
TIME '00:00:00'
+ CAST(AVG(EXTRACT(HOUR FROM COLONNE_TIMESTAMP) * 3600 +
EXTRACT(MINUTE FROM COLONNE_TIMESTAMP) * 60 +
EXTRACT(SECOND FROM COLONNE_TIMESTAMP)) AS INT) * INTERVAL '00:00:01' HOUR TO SECOND
FROM NOM_TABLE
Group by NOM_COLONNE

[McM]

Attention avec les extractions d'heure de timestamp, il donne l'heure UTC

Code :

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

1
2
3
4
5
6
SELECT EXTRACT(HOUR FROM SYSTIMESTAMP), SYSTIMESTAMP, SYSDATE
FROM dual
 
EXTRACT(HOURFROMSYSTIMESTAMP)	 : 14	
SYSTIMESTAMP : 11/01/2019 15:16:28.501618 +01:00
SYSDATE : 11/01/2019 15:16:28

Il est plus simple de transformer le timestamp en Nb de secondes depuis le début de journée, faire l'avg dessus, puis de rajouter ce nombre de seconde à une date 0h (aka Trunc(sysdate)) pour la convertir en HH24:MI:SS

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
SELECT TO_CHAR(TRUNC(SYSDATE) + AVG(TO_NUMBER(TO_CHAR(colonne_timestamp, 'SSSSS'))) / 24 / 60 / 60, 'HH24:MI:SS') heure_moyenne
FROM	
(	SELECT CAST(TO_DATE('10/05/2018 09:06:00', 'DD/MM/RRRR HH24:MI:SS') AS TIMESTAMP) COLONNE_TIMESTAMP FROM dual
UNION ALL SELECT CAST(TO_DATE('28/08/2018 04:01:00', 'DD/MM/RRRR HH24:MI:SS') AS TIMESTAMP) FROM dual
UNION ALL SELECT CAST(TO_DATE('06/08/2018 04:01:00', 'DD/MM/RRRR HH24:MI:SS') AS TIMESTAMP) FROM dual
UNION ALL SELECT CAST(TO_DATE('05/04/2018 09:27:00', 'DD/MM/RRRR HH24:MI:SS') AS TIMESTAMP) FROM dual
UNION ALL SELECT CAST(TO_DATE('21/08/2018 04:03:00', 'DD/MM/RRRR HH24:MI:SS') AS TIMESTAMP) FROM dual
UNION ALL SELECT CAST(TO_DATE('09/06/2018 10:18:00', 'DD/MM/RRRR HH24:MI:SS') AS TIMESTAMP) FROM dual
UNION ALL SELECT CAST(TO_DATE('21/08/2018 04:03:00', 'DD/MM/RRRR HH24:MI:SS') AS TIMESTAMP) FROM dual)
 
HEURE_MOYENNE
06:25:34