räkna inloggningtid

Jag har en loggfil i en mysql 5 , där jag vill plocka ut inloggningstid i minuter.
Målet är att räkna ut hur många minuter en användare har varit inloggad en vecka/månad.
ex:
#55 Kalle 2000 min

Tabellstrukturer
Loggfil:
CREATE TABLE  `database`.`tblagentAction` (
`id` int(11) NOT NULL auto_increment,
`agentid` varchar(10) NOT NULL default '',
`lastaction` varchar(20) NOT NULL default '',
`timestamp` datetime NOT NULL default '0000-00-00 00:00:00',

användare:
CREATE TABLE  `database`.`tblagent` (
  `id` int(11) NOT NULL auto_increment,
  `agentname` varchar(45) NOT NULL default '',

Exempel värden:
6100, '55', 'login', '2008-08-10 12:54:56'
6101, '55', 'logout', '2008-08-10 13:56:35'
6100, '55', 'login', '2008-08-12 12:54:56'
6101, '55', 'logout', '2008-08-12 16:16:15'
6100, '55', 'login', '2008-08-12 17:58:56'
6101, '55', 'logout', '2008-08-12 18:56:35'
6100, '55', 'login', '2008-08-12 19:58:50'
6101, '55', 'logout', '2008-08-12 23:50:15'

CREATE TABLE `database`.`tblagentAction` (
`id` int(11) NOT NULL auto_increment,
`agentid` varchar(10) NOT NULL default '',
`login` datetime NOT NULL default '0000-00-00 00:00:00',
`logout` datetime default NULL

Jag skulle föreslå att du gjorde en designförändring. Jag förusätter att alla vid någon tidpunkt alltid kommer att logga ut. Bygg därför om tabellen så att login sätts vid inloggning och samtidigt sätts logout till NULL. Då kan du enkelt summera tiden för en specifik användare under en specifik period, utan att behöva joina tillbaka mot din tabell.

Men om du inte har möjlighet att designa om din tabell så hjota till igen så hjälper jag dig med frågan.

Värdena som kommer är från en telefonväxel som jag inte kan hantera, så jag kan bara använda informationen som ovan.
Det är betydlig mer information som hamnar i den loggen, men det är bara login och logout jag behöver hantera just nu.

Så har du något tips på vägen vore jag tacksam :)

Typ nåt sånt här?

SELECT ag.agentname, SUM(UNIX_TIMESTAMP(logout.`timestamp`) - UNIX_TIMESTAMP(login.`timestamp`)) 
FROM tblAgent ag 
INNER JOIN tblAgentAction logout ON ag.id = logout.agentid 
INNER JOIN tblAgentAction login ON ag.id = login.agentid 
WHERE 
	logout.lastaction = 'logout' AND 
	logoff.lastaction = 'login' AND
	NOT EXISTS(
		SELECT NULL FROM tblAgentAction a3 
		WHERE 
			a3.agentid = ag.id AND
			a3.lastaction = 'login' AND 
			UNIX_TIMESTAMP(a3.`timestamp`) BETWEEN UNIX_TIMESTAMP(login.`timestamp`) AND UNIX_TIMESTAMP(logout.`timestamp`)
	)
GROUP BY ag.agentname

Kan innehålla fel, buggar, och spår av nötter. Och den är antagligen inte så bra för prestandan.

red. Åtminstone vissa buggar fixade.
red. Ännu lite fixar.

Tack!
Det fungerar inte oich jag vet inte riktigt vad jag ska ändra för att få det korrekt.
För prestandan har det ingen betydelse, blir 1 person som tittar på detta några gånger i veckan :)

Felmeddelande:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[MySQL][ODBC 3.51 Driver][mysqld-5.0.22]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'a3.lastaction = 'login' AND TIMESTAMP(a3.`timestamp`) BETWEEN TIMESTAMP(login.`t' at line 1

Ditt felmeddelande antyder ju på att det är något fel innan "a3.lastaction" och kollar man i koden så verkar det som den missar ett AND eller OR.

a3.agentid = ag.id AND <---------------
a3.lastaction = 'login' AND 
TIMESTAMP(a3.`timestamp`) BETWEEN TIMESTAMP(login.`timestamp`) AND TIMESTAMP(logout.`timestamp`)

Fixade min post ovan.

Nu är det nära, hoppas jag :)

Felmeddelande:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[MySQL][ODBC 3.51 Driver][mysqld-5.0.22]Unknown column 'logoff.lastaction' in 'where clause'

Återigen ger dig felmeddelandet ganska mycket information om vad som gått fel. Det skapas två alias för tabellerna "logout" och "login". Men i where-satsen används "logoff" istället. Den hittar alltså inte "logoff.lastaction", det borde nog vara "login.lastaction".

Fixat i min post. Du kan väl i alla fall försöka lite själv, henkz?

spango skrev:

Fixat i min post. Du kan väl i alla fall försöka lite själv, henkz?

Klart jag testade själv, har även ändrat om ditt nuvarande förslag..
Det gives nu inga felmeddelande, utan den kör tills man får döda processen..

nuvarande kod:
strSQL = "SELECT ag.agentname, SUM(UNIX_TIMESTAMP(logout.`timestamp`) - UNIX_TIMESTAMP(login.`timestamp`)) FROM tblAgent ag INNER JOIN tblAgentAction logout ON ag.id = logout.agentid INNER JOIN tblAgentAction login ON ag.id = login.agentid WHERE logout.lastaction = 'logout' AND login.lastaction = 'login' AND	NOT EXISTS(	SELECT NULL FROM tblAgentAction a3 WHERE a3.agentid = ag.id AND a3.lastaction = 'login' AND UNIX_TIMESTAMP(a3.`timestamp`) BETWEEN UNIX_TIMESTAMP(login.`timestamp`) AND UNIX_TIMESTAMP(logout.`timestamp`)	)GROUP BY ag.agentname"

Om du gör en väldigt, väldigt liten tabell med typ tio poster, funkar det då? Det här kan nämligen som sagt bli sjukt prestandadåligt :)

Jag undrar iofs om det inte borde gå att joina hela vägen för att snabba upp lite. Kanske såhär?

SELECT ag.agentname, SUM(UNIX_TIMESTAMP(logout.`timestamp`) - UNIX_TIMESTAMP(login.`timestamp`)) 
FROM tblAgent ag 
INNER JOIN tblAgentAction logout ON ag.id = logout.agentid 
INNER JOIN tblAgentAction login ON ag.id = login.agentid 
LEFT JOIN tblAgentAction morerecent ON ag.id = morerecent.agentid
WHERE 
	logout.lastaction = 'logout' AND 
	logoff.lastaction = 'login' AND
	UNIX_TIMESTAMP(morerecent.`timestamp`) > UNIX_TIMESTAMP(login.`timestamp`)
GROUP BY ag.agentname
HAVING COUNT(morerecent.`timestamp`) = 0

Tack!
Jag ändrade om din kod ovan och lade även in fler where val/alternativ,
Koden verkar fungera men prestandan blir inget vidare, får döda processerna hela tiden. Blir väl bara att vara utan denna funktion. Kan nog leva utan det.

Tack för att ni tog er tid iallafal! :)

Har du indexerat tblAgentAction.agentid och tblAgent.id? Det bör göra rätt stor skillnad.