Ok, so I have a table on an MySQL server, it has the following columns:
Key, UserID, datetime and position
Position can be “start”, “login” and “end”.
I want to find out the difference between start, sign in and end for each login event.
Anyone have an idea of an SQL query I could use? I think it has something to do with a query within a query?
I postulate that I would first SELECT the start event and then for each “start” event it should look for the next first login event (after the time of the start but less than one hour after the start) then DATE_SUB() to find out the difference. It would also be good to filter out “next” events that happen after a future “start” event in case end didn’t happen.
I would be very grateful if anyone could save me doing this in Excel or Perl.
For anyone’s reference, the final statement looked like this:
SELECT
timediff((select `b`.`datetime` AS `datetime` from `Stats` `b` where ((`b`.`datetime` >= `a`.`datetime`) and (`a`.`UID` = `b`.`UID`) and (`b`.`Service` = _latin1’sign_start’)) order by `b`.`datetime` limit 1),`a`.`datetime`) AS `LoadTime`,
timediff((select `b`.`datetime` AS `datetime` from `Stats` `b` where ((`b`.`datetime` >= `a`.`datetime`) and (`a`.`UID` = `b`.`UID`) and (`b`.`Service` = _latin1’sign_success’)) order by `b`.`datetime` limit 1),`a`.`datetime`) AS `LoginTime`,
timediff((select `b`.`datetime` AS `datetime` from `Stats` `b` where ((`b`.`datetime` >= `a`.`datetime`) and (`a`.`UID` = `b`.`UID`) and (`b`.`Service` = _latin1’sign_failed’)) order by `b`.`datetime` limit 1),`a`.`datetime`) AS `FailedTime`,
timediff((select `b`.`datetime` AS `datetime` from `Stats` `b` where ((`b`.`datetime` >= `a`.`datetime`) and (`a`.`UID` = `b`.`UID`) and (`b`.`Service` = _latin1’sign_failed_retry’)) order by `b`.`datetime` limit 1),`a`.`datetime`) AS `RetryTime`,
`a`.`UID` AS `UID`,`a`.`datetime` AS `EventTime` from `Stats` `a` where (`a`.`Service` = _latin1’start’) order by `a`.`datetime`
This was based on code from my brother which he commented bellow. He used DateDiff which doesn’t give seconds accuracy, also I used Limit 1 instead of TOP because TOP is Microsoft SQL not MySQL. I also realised I shouldn’t use reserved words as field names in a database, it turns out datetime is a reserved word, but I escaped the names to ensure there was no conflict.