title: Using a MySQL query to count records with time ranges at regular intervals
body: <p>Given a MySQL table that tracks how long a visitor spent at a location, what would be a suitable query to count the total visitors at 5 minute intervals over the course of a day?</p>
  
  <pre><code>+-----------------+------------------+------+-----+---------+-------+
  | Field           | Type             | Null | Key | Default | Extra |
  +-----------------+------------------+------+-----+---------+-------+
  | end_timestamp   | int(10) unsigned | NO   | PRI | NULL    |       |
  | start_timestamp | int(10) unsigned | NO   | PRI | NULL    |       |
  | visitor_id      | int(10) unsigned | NO   | PRI | NULL    |       |
  | location_id     | int(10) unsigned | NO   | PRI | NULL    |       |
  +-----------------+------------------+------+-----+---------+-------+
  </code></pre>
  
  <p>E.g. results might look like this:</p>
  
  <pre><code>+---------------------+-------------------+
  | Timestamp           | COUNT(visitor_id) |
  +---------------------+-------------------+
  | 2020-01-01 00:00:00 | 45                |
  | 2020-01-01 00:05:00 | 49                |
  | 2020-01-01 00:10:00 | 37                |
  ...
  </code></pre>
  
  <p>This is something i'm currently calculating post-query but looking to shift some of the work to the MySQL server by doing it as part of the database query.</p>