Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>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 <code>server</code>, then by <code>time</code>. 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 <code>server_status</code> for the purpose of this answer):</p> <pre class="lang-sql prettyprint-override"><code>SELECT *, @currenttime := UNIX_TIMESTAMP(`time`), @lasttime := CASE WHEN server &lt;&gt; @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` </code></pre> <p>As you can see, a temporary variable (<code>@currenttime</code>) is initialised with the UNIX timestamp equivalent of <code>time</code>, 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 <code>offline</code> events).</p> <p>You could now just group the result set produced by the above query, SUM() the <code>seconds_online</code> values and divide them by 60 to get minutes (if you aren't happy with seconds), like this:</p> <pre class="lang-sql prettyprint-override"><code>SELECT server, SUM(seconds_online) DIV 60 AS minutes FROM ( <i>the query above</i> ) s </code></pre> <p>Note, however, that the first query doesn't really calculate the servers' seconds spent online since their respective <em>last</em> 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 <em>per row since the previous row</em>.</p> <p>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 <code>server_status</code> you would have the following as the source table:</p> <pre class="lang-sql prettyprint-override"><code>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 </code></pre> <p>The left part of the UNION ALL just returns all rows from <code>server_status</code>. The right part first gets the last <code>time</code> per server, then joins the result set to <code>server_status</code> to get hold of the corresponding statuses, substituting <code>time</code> with <code>NOW()</code> along the way.</p> <p>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:</p> <pre class="lang-sql prettyprint-override"><code>SELECT server, SUM(seconds_online) DIV 60 AS minutes_online FROM ( SELECT *, @currenttime := UNIX_TIMESTAMP(`time`), @lasttime := CASE WHEN server &lt;&gt; @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 ; </code></pre> <p>And you can try it (as well as play with it) <a href="http://sqlfiddle.com/#!2/e3082/11" rel="nofollow">at SQL Fiddle</a> too.</p>
    singulars
    1. This table or related slice is empty.
    1. POAddition of time differences with where clause in mysql
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USAndriy M
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POAddition of time differences with where clause in mysql
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    1. COWow thank you. This would have to be the longest and most detailed answer I've ever read on this site, pure quality.
      singulars
      1. PO
      1. USGricey
    2. COHappy to be of help!
      singulars
      1. PO
      1. USAndriy M
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload