Databases
StackOverflow2013
Postsdbo
POfind max logons in an interval
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POfind max logons in an interval
    primarykey
    data
    text
    <p>I have a table with <code>timestamps</code> and <code>states</code> for people.</p> <pre><code>|:--------------------------------------------------------------:| | user_id | state | start_time | end_time | |:--------------------------------------------------------------:| | 4711 | 1 | 2013-10-30 09:01:23 | 2013-10-30 17:12:03 | | 4712 | 1 | 2013-10-30 07:01:23 | 2013-10-30 18:12:03 | | 4713 | 1 | 2013-10-30 08:01:23 | 2013-10-30 16:12:03 | | 4714 | 1 | 2013-10-30 09:01:24 | 2013-10-30 17:02:03 | </code></pre> <p>My challenge is, to find out how many users are <code>MAX(logged on) AND AVG(logged on)</code> in same time per Interval. I think that I get out when I can see, how many users are simultaneously logged in per second.</p> <pre><code>|:-------------------------------------:| | timestamp | state | userid | |:-------------------------------------:| | 1383123683 | 1 | 4711 | | 1383123684 | 1 | 4711 | | 1383123684 | 1 | 4712 | | 1383123685 | 1 | 4711 | | 1383123685 | 1 | 4712 | | ... | ... | ... | </code></pre> <p>By the way, one intervals is a quarter of an hour.</p> <p>The Data comes via <code>INSERT INTO</code> so my idea was to crate a trigger and wrote in a helper table one row for each second (UNIX timestamp) between start and end adding the <code>state_id</code>.</p> <p>At the end, it must be possible to group over the seconds and count over the <code>datasets</code> to find out, how many rows are exist in one second. For the <code>AVG</code> I have not yet a formula :-). It's a question of time, you know.</p> <p>But I'm not sure, if my idea was a good one, because i fear that my plan needs a lot of performance and space.</p> <p>The better idea will be, to wrote just the <code>start-time</code> and <code>end-time</code>, but i loosing the possibility to grouping over the seconds. </p> <p>How I can manage that without thousands of rows in my database?</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USFrank
    1. USFrank
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POfind max logons in an interval
      1. This table or related slice is empty.
      1. VTApproveEditSuggestion
    1. COI'm not sure that I understand phrase _By the way, one intervals is a quarter of an hour_ do you mean that start_time and end_time always ends with `:0:0` `:15:0` `:30:0` `45:0` ? Another question - is there possibility for user to have overlapped loggedin times?
      singulars
      1. POfind max logons in an interval
      1. USIłya Bursov
    2. COThe interval is just an info. the max value should be calculated for an interval. I think this information is not so important for the solution. Yes, users can have overlapping login_times, as my example shows
      singulars
      1. POfind max logons in an interval
      1. USFrank
 

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