Zeitdifferenz mit LEAD ermitteln

[master-sp]

Guten Tag Gemeinde,
ich habe eine kleine Tabelle in der die Ein-/Ausschaltpunkte protokolliert werden.



Nun nehme ich die Funktion LEAD und ziehe von dem zweiten Zeitstempel den ersten Zeitstempel ab.

SELECT PCC, TimeStamp AS log_Zeit,
Cast(lead(TimeStamp) OVER (PARTITION BY JobID ORDER BY TimeStamp) - TimeStamp AS TIME) AS gesZeit
FROM [KSM].[dbo].[PlantControlCondition]
order by Timestamp asc

Die Abfrage kommt dann folgend zurück



Es werden nicht die korrekten Zeiten über mehrere Tage ausgegeben ... z.B. wenn ich 72 Stunden Differenz hätte. Kann es sein, dass ich die Timestamp noch Konvertieren muss, nur wie ...

Gruß MaWe

 

[akretschmer]

solche Bildchen sind ganz einfach scheiße zu lesen und die Werte auch nicht via Copy&Paste zu übernehmen, aber soweit ich das sehe gibt es in der Tabelle kein Feld JobID, nachdem Du PARTITION BY machst. Das kann also gar nicht gehen und sollte in einer Fehlermeldung gipfeln.

 

[ukulele]

Datum von Datum subtrahieren ist nicht so schön, ideal wäre datediff() zu verwenden. Das wird allerdings nicht das Problem sein sondern dein cast( <datum_mit_zeit> AS TIME) berücksichtigt eventuell nur den Zeitanteil und keine Tage. Versuche dich an expliziter Konvertierung mit convert() und einem Format. Hier ist der Microsoft Artikel gar nicht mal so schlimm wie man annehmen würde

CAST und CONVERT (Transact-SQL) - SQL Server

Transact-SQL-Referenz für die Funktionen CAST und CONVERT Mit diesen Funktionen wird ein Ausdruck von einem Datentyp in einen anderen konvertiert.

learn.microsoft.com

 

[MDDaniel]

Wenn ich die Tabelle richtig interpretiere bedeutet der Wert 1 in PCC Zeitpunkt des Einschaltens und 0 das Ausschalten.
Daher müsstest du immer die Einschaltzeitpunkte vom nächsthöheren Ausschaltpunkt abziehen.
Jetzt ziehst du auch den Ausschaltpunkt vom nächsthöheren Einschaltschaltpunkt ab, womit die Summe der Gesamtzeit die komplette Zeit vom ersten bis zum letzten Zeitstempel umfasst.
Sehe ich es richtig, dass du eigentlich nur für jedes Ausstempeln die Dauer vom vorherigen Einstempeln benötigst?

 

[dabadepdu]

Daher müsstest du immer die Einschaltzeitpunkte vom nächsthöheren Ausschaltpunkt abziehen.

Genau, dann noch das Problem mit dem Typfehler, den @ukulele angemerkt hat. Wobei ich "glaube", das Time auch mit Werten größer 24h klar kommt und das Problem mit JOB_ID, deren Wert / Bedeutung hier nicht zu sehen ist.
Mit Lag und Lead muss man auch beachten / sicherstellen, dass es keine Datenlücken gibt. Werden die Schaltpunkte nicht alle(!) ordentlich eingetragen, so kann das auch schön in die Hose gehen, wenn 2 oder mehr Ereignisse vom gleichen Typ aufeinander folgen.

 

[t-sql]

Beim Datentyp TIME wird nur der Zeitanteil betrachtet und d.h. 24 Std. Siehe hier time (Transact-SQL) - SQL Server

 

[master-sp]

Wenn ich die Tabelle richtig interpretiere bedeutet der Wert 1 in PCC Zeitpunkt des Einschaltens und 0 das Ausschalten.
Daher müsstest du immer die Einschaltzeitpunkte vom nächsthöheren Ausschaltpunkt abziehen.
Jetzt ziehst du auch den Ausschaltpunkt vom nächsthöheren Einschaltschaltpunkt ab, womit die Summe der Gesamtzeit die komplette Zeit vom ersten bis zum letzten Zeitstempel umfasst.
Sehe ich es richtig, dass du eigentlich nur für jedes Ausstempeln die Dauer vom vorherigen Einstempeln benötigst?

Ja, genau ... es darum, die Zeit zwischen der Ein- / Ausschaltpunkten zu ermitteln.

 

[master-sp]

Genau, dann noch das Problem mit dem Typfehler, den @ukulele angemerkt hat. Wobei ich "glaube", das Time auch mit Werten größer 24h klar kommt und das Problem mit JOB_ID, deren Wert / Bedeutung hier nicht zu sehen ist.
Mit Lag und Lead muss man auch beachten / sicherstellen, dass es keine Datenlücken gibt. Werden die Schaltpunkte nicht alle(!) ordentlich eingetragen, so kann das auch schön in die Hose gehen, wenn 2 oder mehr Ereignisse vom gleichen Typ aufeinander folgen.

Das mit den korrekten Dateneintragungen ist mir klar. Die Funktion arbeitet auch korrekt, nur die Gesamtzeit ist falsch, wenn es ein Zeitrahmen über 24h ist.

 

[master-sp]

Beim Datentyp TIME wird nur der Zeitanteil betrachtet und d.h. 24 Std. Siehe hier time (Transact-SQL) - SQL Server

Danke für den Hinweis, nur kann ich mit einer anderen Zeitbasis als TIME keine subtraction durchführen. Also mit ist nicht klar wie ich die beiden Timestamp von einander subtrahieren kann

 

[t-sql]

Danke für den Hinweis, nur kann ich mit einer anderen Zeitbasis als TIME keine subtraction durchführen. Also mit ist nicht klar wie ich die beiden Timestamp von einander subtrahieren kann

Datums bzw. Zeit Berechnung ist nicht wirklich trivial. Da mußte dich einlesen. Es gibt dazu gute Dokumentation mit Beispielen. Aber bei Dir würde ich mal mit Datediff anfangen. Hier mal ein Beispiel How to get total number of hours between two dates in sql server?

 

[ukulele]

Danke für den Hinweis, nur kann ich mit einer anderen Zeitbasis als TIME keine subtraction durchführen. Also mit ist nicht klar wie ich die beiden Timestamp von einander subtrahieren kann

datediff() ist das Mittel der Wahl.

Anstelle von lag() und lead() ist manchmal ein Join das Mittel der Wahl. ich gehe mal davon aus das JobID gegeben ist:

WITH t AS (
    SELECT    ROW_NUMBER() OVER (PARTITION BY JobID ORDER BY [TimeStamp]) AS zeile,
            *
    FROM    [KSM].[dbo].[PlantControlCondition]
    )
SELECT    *,
        datediff(minute,t1.[TimeStamp],t2.[TimeStamp]) AS Minuten
FROM    t t1
LEFT JOIN t t2
ON        t1.JobID = t2.JobID
AND        t1.zeile + 1 = t2.zeile
AND        t2.PCC = 0
WHERE    t1.PCC = 1

Wenn man jetzt keine Ausgabe in Minuten will ist das sicherlich machbar, zur Not "zu Fuß", aber erstmal müssen ja die Daten stimmen, dann kann man sich über das Ausgabeformat Gedanken machen.

PS: Damit kann man dann auch Lücken erkennen und behandeln, mit lag() und lead() ist das nicht so einfach der Fall, wie bereits erwähnt. Wenn hier t2 NULL ist dann ist das entweder eine fehlende Endzeit oder ein noch laufender Vorgang.

 

[master-sp]

Hallo Gemeinde,
vielen Dank für die Anregungen!!

Hier meine Lösung für das Problem:

SELECT  PCC, TimeStamp AS log_Zeit,
                replicate('0', 2 - len(DATEPART(hour, (Cast(lead(TimeStamp) OVER (ORDER BY TimeStamp) AS datetime) - Cast(TimeStamp as datetime))) + (Datepart(Day, (Cast(lead(TimeStamp) OVER (ORDER BY TimeStamp) AS datetime) - Cast(TimeStamp as datetime)))-1) *24))
                +Cast(DATEPART(hour, (Cast(lead(TimeStamp) OVER (ORDER BY TimeStamp) AS datetime) - Cast(TimeStamp as datetime))) + (Datepart(Day, (Cast(lead(TimeStamp) OVER (ORDER BY TimeStamp) AS datetime) - Cast(TimeStamp as datetime)))-1) *24 as varchar(2)) +':'
                +replicate('0', 2 - len(DATEPART(MINUTE, (Cast(lead(TimeStamp) OVER (ORDER BY TimeStamp) AS datetime) - Cast(TimeStamp as datetime)))))
                +Cast(DATEPART(MINUTE, (Cast(lead(TimeStamp) OVER (ORDER BY TimeStamp) AS datetime) - Cast(TimeStamp as datetime))) as varchar(2)) +':'
                +replicate('0', 2 - len(DATEPART(SECOND, (Cast(lead(TimeStamp) OVER (ORDER BY TimeStamp) AS datetime) - Cast(TimeStamp as datetime)))))
                +Cast(DATEPART(SECOND, (Cast(lead(TimeStamp) OVER (ORDER BY TimeStamp) AS datetime) - Cast(TimeStamp as datetime))) as varchar(2)) as abs_Zeit,
                (CASE WHEN (PCC = 1) THEN 'Produktion über Anlagensteueurng aktiviert' ELSE 'Produktion über Anlagensteueurng deaktiviert' END) AS Kommentar
FROM            [KSM].[dbo].[PlantControlCondition]

Ergebnis in der Sicht:

Die "ges. Tage" werden in "Stunden" umgerechnet und aufaddiert.



Anzeige im Grafana Dashboard:




Vielen DANK für eure Hilfe!!