Bob Doing Bob Things

  • Blog
  • Technology
  • Travelling
  • Business
  • Archive

Query Question

Posted on 2011-03-16 by bobdvb

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.

 

Posted in TechnologyTagged _latin1, Bob H, datetime, difference, end, event, happen, limit, login, names, order, query, reserved, select, service, sql, start, stats, timediff, uid, used

Post navigation

← Moving on to Pastures New
Power Generation: The Arguments →

Leave a Reply Cancel Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Recent Posts

  • Home server challenges – Special Motherboards
  • In search of non-“Line of sight” WiFi
  • Logging water pressure with Arduino and Thingspeak
  • Migrating to WordPress
  • Teardown of Sinotimer TM609 WiFi Switch

Archives

  • December 2020
  • September 2020
  • August 2020
  • July 2020
  • November 2016
  • August 2011
  • July 2011
  • June 2011
  • May 2011
  • March 2011
  • November 2010
  • October 2010
  • July 2010
  • June 2010
  • May 2010
  • April 2010
  • March 2010
  • February 2010
  • January 2010
  • December 2009
  • November 2009
  • October 2009
  • August 2009
  • July 2009
  • June 2009
  • May 2009
  • April 2009
  • March 2009
  • February 2009
  • January 2009
  • December 2008
  • November 2008
  • October 2008
  • September 2008
  • August 2008
  • June 2008
  • May 2008
  • April 2008
  • March 2008
  • February 2008
  • January 2008
  • December 2007
  • November 2007
  • October 2007
  • September 2007
  • July 2007

Categories

  • Archive
  • Blog
  • Business
  • Technology
  • Travelling
  • Wandering round in fields of idiots
Powered By DreamHostSpecial Thanks