Addition of time differences with where clause in mysql

Question

I have a data table with hundreds of thousands of rows which represent requests to servers to get data. Each record has a timestamp, the server ID and a binary value (tinyint) of whether the server responded correctly. The query times are not constant.

I am trying to get a total amount of time that the server was deemed to be 'online' by adding up the times between the queries where the server was online (very highly preferable a mysql query). Eg.

server | time | status
1 | 1/1/2012 11:00 online
1 | 1/1/2012 11:02 online
1 | 1/1/2012 11:05 offline
2 | 1/1/2012 11:10 online
1 | 1/1/2012 11:30 online
Time now: 11:40
Server 1 Online Time = 2+3+10 = 15 minutes

Is it possible to do this in mysql? I would much prefer it over getting all the rows to php and calculating it or averaging anything.

Answer 1

This could be done using UNIX timestamp conversion and variable assignment on a properly sorted row set. By "properly sorted" I mean the rows must be sorted by server, then by time. Here's how you could use variables to get the online time (interval) in seconds since the previous event for every row in your table (called server_status for the purpose of this answer):

SELECT
  *,
  @currenttime := UNIX_TIMESTAMP(`time`),
  @lasttime := CASE
    WHEN server <> @lastserver OR @laststatus = 'offline'
    THEN @currenttime
    ELSE @lasttime
  END,
  @currenttime - @lasttime AS seconds_online,
  @lasttime := @currenttime,
  @lastserver := server,
  @laststatus := status
FROM
  server_satus s,
  (SELECT @lastserver := 0) x
ORDER BY
  s.server,
  s.`time`

As you can see, a temporary variable (@currenttime) is initialised with the UNIX timestamp equivalent of time, another one is used to hold the previous timestamp so that the difference between the two could be calculated. Other variables are used to remember the previous server ID and the previous status, so that, when necessary, the difference was returned as 0 (which is done for every row which records a server's first event as well as those that come after offline events).

You could now just group the result set produced by the above query, SUM() the seconds_online values and divide them by 60 to get minutes (if you aren't happy with seconds), like this:

SELECT
  server,
  SUM(seconds_online) DIV 60 AS minutes
FROM (
  the query above
) s

Note, however, that the first query doesn't really calculate the servers' seconds spent online since their respective last events. That is, the current time might very well differ from that in any of the latest event records, and it wouldn't be taken into account, because the query calculates the seconds per row since the previous row.

One way to solve this would be to add one row per server containing the current timestamp and the same status as in the last record. So, instead of just server_status you would have the following as the source table:

SELECT
  server,
  `time`,
  status
FROM server_status

UNION ALL

SELECT
  s.server,
  NOW() AS `time`,
  s.status
FROM server_status s
  INNER JOIN (
    SELECT
      server,
      MAX(`time`) AS last_time
    FROM server_status
    GROUP BY
      server
  ) t
    ON s.server = t.server AND s.`time` = t.last_time

The left part of the UNION ALL just returns all rows from server_status. The right part first gets the last time per server, then joins the result set to server_status to get hold of the corresponding statuses, substituting time with NOW() along the way.

Now that the table is completed with the "fake" event rows reflecting the current time, you can apply the method used in the first query. Here's what the final query looks like:

SELECT
  server,
  SUM(seconds_online) DIV 60 AS minutes_online
FROM (
  SELECT
    *,
    @currenttime := UNIX_TIMESTAMP(`time`),
    @lasttime := CASE
      WHEN server <> @lastserver OR @laststatus = 'offline'
      THEN @currenttime
      ELSE @lasttime
    END,
    @currenttime - @lasttime AS seconds_online,
    @lasttime := @currenttime,
    @lastserver := server,
    @laststatus := status
  FROM
    (
      SELECT
        server,
        `time`,
        status
      FROM server_status

      UNION ALL

      SELECT
        s.server,
        NOW() AS `time`,
        s.status
      FROM server_status s
        INNER JOIN (
          SELECT
            server,
            MAX(`time`) AS last_time
          FROM server_status
          GROUP BY
            server
        ) t
          ON s.server = t.server AND s.`time` = t.last_time
    ) s,
    (SELECT @lastserver := 0) x
  ORDER BY
    s.server,
    s.`time`
) s
GROUP BY
  server
;

And you can try it (as well as play with it) at SQL Fiddle too.

Answer 2

Here is the sample table structure I created:

-- SQL EXAMPLE
CREATE TABLE IF NOT EXISTS `stack_test` (
  `server` int(11) NOT NULL,
  `rtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `status` tinyint(4) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `stack_test` (`server`, `rtime`, `status`) VALUES
    (1, '2012-01-01 11:00:24', 1),
    (1, '2012-01-01 11:02:24', 1),
    (1, '2012-01-01 11:05:24', 0),
    (2, '2012-01-01 11:10:24', 1),
    (1, '2012-01-01 11:30:24', 1);
-- SQL EXAMPLE END

This is the PHP code:

<?php
$query = 'SELECT DISTINCT(`server`) `server` FROM stack_test';
$res = sql::exec($query); // replace with your function/method to execute SQL
while ($row = mysql_fetch_assoc($res)) {
    $server = $row['server'];
    $uptimes = sql::exec('SELECT * FROM stack_test WHERE server=? ORDER BY rtime DESC',$server);
    $online = 0;
    $prev = time();
    $prev = strtotime('2012-01-01 11:40:00'); // just to show that it works given the example
    while ($uptime = mysql_fetch_assoc($uptimes)) {
        if ($uptime['status'] == 1) {
            echo date('g:ia',$prev) . ' to ' . date('g:ia',strtotime($uptime['rtime'])) . ' = '.(($prev-strtotime($uptime['rtime']))/60).' mins<br />';
            $online += $prev-strtotime($uptime['rtime']);
        }
        $prev = strtotime($uptime['rtime']);
    }
    echo 'Server '.$server.' is up for '.($online/60).' mins.<br />';
}
?>

This is the output I get:

11:40am to 11:30am = 10 mins
11:05am to 11:02am = 3 mins
11:02am to 11:00am = 2 mins
Server 1 is up for 15 mins.

11:40am to 11:10am = 30 mins
Server 2 is up for 30 mins.