Calculate time interval between 2 dates on different rows.

Question

Hello.

I have login and logout events and i need to calculate time between them.

I guess i could group each 2 rows (each two messages) and then do the calculation, but how would you do that?

Example XML i need to query:

<Log>
  <Message>
    <DateTime>2009-12-02 14:38:41</DateTime>
    <Priority>Local3.Info</Priority>
    <Source_Host>192.168.0.100</Source_Host>
    <MessageText>Dec  2 14:38:41 root: login,ng1,192.168.0.160,janis.veinbergs</MessageText>
  </Message>
  <Message>
    <DateTime>2009-12-02 15:28:19</DateTime>
    <Priority>Local3.Info</Priority>
    <Source_Host>192.168.0.100</Source_Host>
    <MessageText>Dec  2 15:30:33 root: logout,ng1,,janis.veinbergs</MessageText>
  </Message>
  <Message>
    <DateTime>2009-12-02 15:29:11</DateTime>
    <Priority>Local3.Info</Priority>
    <Source_Host>192.168.0.100</Source_Host>
    <MessageText>Dec  2 15:31:25 root: login,ng1,192.168.0.160,janis.veinbergs</MessageText>
  </Message>
  <Message>
    <DateTime>2009-12-02 15:58:22</DateTime>
    <Priority>Local3.Info</Priority>
    <Source_Host>192.168.0.100</Source_Host>
    <MessageText>Dec  2 16:00:37 root: logout,ng1,,janis.veinbergs</MessageText>
  </Message>
</Log>

Thankyou.

Answer 1

Given SQL doesn't have any aggregate Diff method, I would suggest joining the table onto itself, and selecting each row that you're after from each side of the join.

Something like:

var diff = from a in db.Events
           join b in db.Events on a.SessionId equals b.SessionId
           where a.EventType == 'Login' && b.EventType == 'Logout'
           select b.EventTime - a.EventTime;

Haven't tried this, but something along those lines should work.

---

EDIT: updated to suit new provided info.

Try the following. Could perhaps be more concise, but does the job. Have broken down into a couple of queries for easier reading.

var query = from a in (from log in data.Elements()
                       select new {
                          date = DateTime.Parse(log.Element("DateTime").Value),
                          msg = log.Element("MessageText").Value
                       })
            select new {
       a.date,
       type = a.msg.Contains("login") ? "Login" : "Logout",
       user = a.msg.Substring(a.msg.LastIndexOf(',') + 1)
      };

var results = from a in query
   join b in query on a.user equals b.user
   where a.type == "Login" && b.type == "Logout"
    && b.date == (query.OrderBy(o => o.date).Where(d => d.date > a.date).FirstOrDefault().date)
   select new {
    a.user,
    Login = a.date,
    Logout = b.date    
   };