Databases
StackOverflow2013
Postsdbo
POMySQL display statistics grouped by time slot
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMySQL display statistics grouped by time slot
    primarykey
    data
    text
    <p>I have one table called reservation with such fields:</p> <pre><code>`id` int(11) NOT NULL AUTO_INCREMENT, `username` varchar(150) NOT NULL, `begin_time` datetime NOT NULL, `end_time` datetime NOT NULL, `node_id` int(11) NOT NULL, PRIMARY KEY (`id`) </code></pre> <p>I wish to show statistics about reservations during a given time period (say $from - $until, values coming from php). A user books one node with each reservation. There are many nodes which can be booked and I use the following query to show reservation statistics by date.</p> <pre><code>SELECT DATE(begin_time) `Date`, count(id) as reservations FROM reservation WHERE begin_time &gt; '" . $from . "' AND end_time &lt; '" . $until . "' GROUP BY Date" </code></pre> <p>Now I want to give statistics on which time period during the day is more active. The 24 hours of a day are divided in 48 thirty-minute slots. In my table, all the begin_date and end_date values are round numbers like 2011-04-11 20:00:00 or 2011-04-11 14:30:00. Reservations can have a duration of 30 minutes, 60 minutes, etc until 4 hours maximum.</p> <p>I would like my statistics to be in the following format (more or less)</p> <pre><code> Time slot | reservations ---------------------------------- 00:00:00 - 00:30:00 | 23 00:30:00 - 01:00:00 | 12 01:00:00 - 01:30:00 | 20 01:30:00 - 02:00:00 | 66 02:00:00 - 02:30:00 | 12 and so on </code></pre> <p>Can you help me with the SQL query?</p> <p><strong>Edit:</strong> I see now that PostgreSQL has some range types and operators, for example &lt;@ (range is contained by) <a href="http://www.postgresql.org/docs/9.2/interactive/functions-range.html" rel="nofollow">documentation here</a>. Does this mean that my query would be easier if I used Postgres? Does MySQL have something similar?</p> <p><strong>Edit 2:</strong> Thank you, tombom. Actually, I changed your answer, removing DATE(begin_time) AS <code>Date</code> from the select clause and the group by clause, because I wanted to see which hours of the day are the most active <em>over a period of many days</em>. And I can live with having 1 hour granularity instead of 30 mins. But I would like to ask you what exactly you mean with your first sentence. If a reservation starts at 01:00:00 and it ends at 03:00:00 I do in fact want it to be counted on both time slots.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USengineerX
    1. USengineerX
    plurals
    1. This table or related slice is empty.
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. This table or related slice is empty.
 

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