J'ai 2 colonnes dans ma table dans le server sql – [Temps d'Occurrence (NT)] et [Temps de Dégagement (NT)] ayant le format aaaa-mm-jj hh: mm: ss et une troisième colonne [Durée d'interruption] avec le format hh: mm: ss
[Identifiant] | [Temps d'apparition (NT)] | [Temps de dédouanement (NT)] | [Durée de la panne]
4 | 2014-12-28 15: 06: 33.000 | 2014-12-28 15: 18: 18.000 | 00: 11: 45.000
La durée d'interruption est calculée comme la différence de [Temps d'occurrence (NT)] et [Temps de libération (NT)] Actuellement, mon code pour le faire est le suivant:
select distinct a.[Identifier] ,a.[Occurrence Time(NT)] ,a.[Clearance Time(NT)] ,cast((cast(a.[Clearance Time(NT)] as datetime) - cast(a.[Occurrence Time(NT)]as datetime)) as time ) as [Outage Duration] from final_report_2 a
La dissortingbution est juste une security car les deux colonnes sont déjà datetime. Ce code fonctionne pour tous les cas où [Temps d'apparition (NT)] et [Temps de libération (NT)] tombent le même jour, c'est-à-dire que la panne est dans les 24 heures
Par exemple dans la rangée ci-dessus no. 4 ont tous les deux le 28ème jour et ainsi la panne est calculée correctement.
Mais la durée de la panne est calculée à tort en cas de jours différents.
2678 | 2014-12-28 12: 50: 04.000 | 2014-12-31 23: 59: 59.000 | 11: 09: 55.000
Au rang 2678, l'heure devrait être 83:09:55 au lieu de 11:09:55.
J'ai donc besoin de prendre en count la différence des jours, puis de calculer la [Durée de l'interruption] au format hh: mm: ss.
L'un des moyens possibles de le faire est le suivant:
(23:59:59 – 12:50:04) + 00:00:01 + ((31-28-1) * 24): 00: 00 + 23:59:59
où la première partie calcule l'heure du premier jour, alors la seconde partie calcule le non. de jours entiers entre le [Temps Occurrence (NT)] et le [Temps de Liquidation (NT)] et le multiplie par 24 et la partie finale représente l'heure du dernier jour.
Comment puis-je implémenter le ci-dessus dans le server sql? Peut-il être fait en utilisant les fonctions DATEPART ou DATEADD?
vous devez utiliser la fonction datediff
.
Trouvez d'abord la difference
en seconds
entre les deux dates.
select Datediff(SECOND, [Clearance Time(NT)], [Occurrence Time(NT)])
Maintenant, convertissez les seconds
en hh:mm:ss
utilisant ce code.
DECLARE @TimeinSecond INT SET @TimeinSecond = 86399 -- Change the seconds SELECT RIGHT('0' + CAST(@TimeinSecond / 3600 AS VARCHAR),2) + ':' + RIGHT('0' + CAST((@TimeinSecond / 60) % 60 AS VARCHAR),2) + ':' + RIGHT('0' + CAST(@TimeinSecond % 60 AS VARCHAR),2)
Changez votre requête quelque chose comme ça.
SELECT DISTINCT a.[Identifier], a.[Occurrence Time(NT)], a.[Clearance Time(NT)], RIGHT('0' + Cast(Datediff(SECOND, [Clearance Time(NT)],[Occurrence Time(NT)]) / 3600 AS VARCHAR), 2) + ':' + RIGHT('0' + Cast((Datediff(SECOND, [Clearance Time(NT)],[Occurrence Time(NT)]) / 60) % 60 AS VARCHAR), 2) + ':' + RIGHT('0' + Cast(Datediff(SECOND, [Clearance Time(NT)],[Occurrence Time(NT)]) % 60 AS VARCHAR), 2) [Outage Duration] FROM final_report_2 a
Référence: conversion des seconds
en hh:mm:ss
partir de ce lien
Bon vieux SQL DATEDIFF. Voici la fonction de durée que j'utilise. Tout enveloppé dans un UDF.
CREATE FUNCTION fDuration(@DtStart DATETIME, @DtEnd DATETIME) RETURNS VARCHAR(10) AS BEGIN RETURN ( SELECT CONVERT(VARCHAR(10), t.Hours) + ':' + RIGHT('00' + CONVERT(VARCHAR(10), t.Minutes), 2) + ':' + RIGHT('00' + CONVERT(VARCHAR(10), t.Seconds - (t.Hours * 60 * 60) - (t.Minutes) * 60), 2) as Value FROM ( SELECT t.Hours, ABS(t.Seconds / 60 - (t.Hours * 60)) as Minutes, t.Seconds FROM ( SELECT DATEDIFF(SECOND, @DtStart, @DtEnd) as Seconds, DATEDIFF(SECOND, @DtStart, @DtEnd)/60/60 AS Hours) t ) t) END
USAGE:
SELECT dbo.fDuration('2014-12-28 12:50:04.000', '2014-12-31 23:59:59.000')
RÉSULTATS :
83:09:55
Dans votre cas, vous pouvez modifier votre requête comme ceci
SELECT DISTINCT a.[Identifier], a.[Occurrence Time(NT)], a.[Clearance Time(NT)], dbo.fDuration(a.[Occurrence Time(NT)], a.[Clearance Time(NT)]) as [Outage Duration] FROM final_report_2 a
Ou même append [Durée d'interruption] en tant que champ calculé dans final_report_2 avec la formule
dbo.fDuration([Occurrence Time(NT)],[Clearance Time(NT)])
Ensuite, il devient un simple choix …
SELECT DISTINCT a.[Identifier], a.[Occurrence Time(NT)], a.[Clearance Time(NT)], a.[Outage Duration] FROM final_report_2 a
Remarque Les heures ne sont pas préfixées avec un '0'. Cela permet des durées négatives.
J'espère que cela pourra aider