Userlogging und Zeitauswertung

Ich habe ine Tabelle für Benutzer, die den Status und einen Zeitstempel speichert

realuser (integer) {userid}
state (integer) {0 = ausgelogged, 1 = eingelogged}
date (timestamp without time zone) {Zeitstempel des Eintrags}

Nun möchte ich mit einer SQL Abfrage die zusammenaddierte eingeloggte Zeit haben. Also die Zeiträume zwischem einen State 1 und State 0 Wechsel.

Mein bisheriger Ansatz liefert leider Rückgaben im Jahresbereich auf einen Tag addiert, wo ich also vermute, dass er mehr also nur eines addiert.

Problem verstanden? Ich hoffe ich konnte es klar formulieren.

Das ist mein bisheriger Ansatz, date_trunc reduziert den Zeitstempel auf den kompletten Tag, so dass ich gruppieren kann:

SELECT l1.realuser, date_trunc(‘day’, l1.date) as date2,
sum(l2.date - l1.date) AS sumtime
FROM
realuserlog l1, realuserlog l2
WHERE
l2.date > l1.date AND l1.state = 1 AND l2.state = 0
GROUP BY
l1.realuser, date2
ORDER BY
date2 ASC;

Hallo,

warum schreibst Du die TIMESTAMP’s für das Ein- und Ausloggen eines Users nicht in einen Datensatz? Dadurch wird Auswertung wesentlich einfacher!

Schau Dir deine GROUP BY definition mal genauer an. Welches Ergebnis willst Du haben und in welcher Reihenfolge muss dann die Summenbildung erfolgen?



mfg

fristo

Weil der User mehr als nur zwei States hat, die im Laufe der Entwicklung sehr wahrscheinlich noch erweitert werden. Und dann möchte ich nicht jedesmal Tabellen verbreitern :slight_smile: Zumal nicht jeder Statewechsel immer in eine Zeile gehört. Es gibt zum Beispiel noch pausieren und aktivieren und besetzt, und die können auch in beliebiger Zahl zwischen Login und Logout vorkommen.

[quote=“firegate666”]
Mein bisheriger Ansatz liefert leider Rückgaben im Jahresbereich auf einen Tag addiert, wo ich also vermute, dass er mehr also nur eines addiert.
[/quote]

Schau Dir deine GROUP BY definition mal genauer an. Welches Ergebnis willst Du haben und in welcher Reihenfolge muss dann die Summenbildung erfolgen?

mfg

fristo

Das verstehe ich grad nicht…

Die Summen brauche ich über zwei aufeinanderfolgende Statewechsel, Zählbeginn bei Wechsel auf 1 und Zählende bei Wechsel auf 0, also ein LoggedIn Zeitraum. Dieser kann am Tage mehrfach vorkommen, weswegen die Zeiten dort summiert werden müssen.

Ganz offensichtlich ist es aber leider nicht so, dass er eben den erste State 1 nimmt und dann den darauffolgenden State 0, sondern eben erst die Summe 1 zum nächsten 0, dann noch mal die gleiche 1 zur übernächsten 0 und so weiter, bis er alle durch hat. Dann nimmt er die nächste 1.[/quote]

Hallo,

Prima. Nur ein bestimmter Zustand startet und endet zu einer Zeit und diese Zeiten gehören zusammen und sollten in einen Datensatz stehen.

Egal wie Du Dein Konzept gestalltest, für die Auswertung brauchst Du genau diese Art von Datensätze. Also gehe in kleinen Schritten vor und erstelle zuerst mal die Grundtabelle, bestehend aus dem TIMESTAMP für das Einloggen, den zugehörigen TIMESTAMP für das Auslogggen und dem User, für den die TIMESTAMP’s gelten. Auf diesen SELECT kannst Du dann die Auswertung aufbauen. Wenn Du nicht mehr weiter kommst, kannst Du dich ja nochmals melden.



mfg

fristo

Ähm, wenn ich an das Beispiel radius denke, kann ich mir durchaus Situationen vorstellen, bei denen ein Start- und ein Stopdatensatz denkbar ist. Der Server kann gar nicht den State für alle Datensätze halten, bis irgendwann mal vielleicht ein Stop kommt. Also werden zwei Datensätze geschrieben.

Mir fällt auf Anhieb auch nur ein, dafür eine Funktion zu schreiben und/oder mit einem Subselect ausgehend vom aktuellen Datensatz den nächsten zu suchen.

Ansonsten: beim Schreiben eines Datensatzes könntest du vielleicht noch (mit einem Trigger und einer Funktion z.B.) den Start Datensatz heraussuchen und den updaten …

Hallo ads,

mein Vorschlag bedeutet nicht, dass “firegate666” sein Konzept nicht weiter verfolgen soll. Für die Auswertung bleibt ihm aber nichts anders überig die entsprechende Grundtabelle für die Auswertung zu erstellen.

Wenn man Start- / Stop-Datensätze unbedingt verwenden will, sollte man doch auch mal so einige Randbedingungen betrachten. Wie sieht es z.B aus wenn die Clientverbindung unterbrochen wird oder der Server abstürzt … . Zumindest fehlt dann der Stop-Datensatz. Der SELECT für Grundtabelle wird dann noch aufwendiger und komplizierter. Mein Vorschlag beinhaltet dagegen immer einen vollständigen Datesatz, wobei ich den TIMESTAMP für den Stop-Wert immer mit NULL initialisieren würde. Und der SQL-Befehl für die Auswertung dürfte sich auch einfacher gestallten.

Hast Du eine bessere Idee?



mfg

fristo

Du läufst zwangsläufig in die gleichen Probleme wie überall anders auch, wo du Session Daten nicht über eine Datenbank Session hinweg halten kannst. Bei Radius ist das Problem ähnlich und beim Login/Logout über eine DB mit Accounting auch.

Man definiert entweder Timeouts oder der nächste Start Record gilt als Stop für den vorherigen Start.

Die Lösungen sind sicherlich nicht perfekt, die Anforderungen jedoch kommen aus der realen Welt …