title: MySQL Split Time Ranges into Smaller Chunks
body: <p>I've recently been tasked with finishing a PHP/MySQL web app when the developer quit last week. I'm no MySQL expert, so I apologize if this is an intensely simple question. I've searched SO for the better part of two days trying to find a relatively easy solution to my problem, which is as follows. </p>
  
  <p>Problem in a Nutshell:<br>
  I have a MySQL table full of start and end datetime (GMT -5) &amp; UNIX Timestamp values covering durations of irregular length and need to break/split/divide them into more-regular time chunks (5 minutes). I'm not after a count of row entries per time chunk/bucket/period, if that makes any sense.</p>
  
  <p>Data Example:  </p>
  
  <pre><code>started, ended, started_UNIX, ended_UNIX  
  2010-10-25 15:12:33, 2010-10-25 15:47:09, 1288033953, 1288036029  
  </code></pre>
  
  <p>What I'm hoping to get:  </p>
  
  <pre><code>2010-10-25 15:12:33, 2010-10-25 15:15:00, 1288033953, 1288037700  
  2010-10-25 15:15:00, 2010-10-25 15:20:00, 1288037700, 1288038000  
  2010-10-25 15:20:00, 2010-10-25 15:25:00, 1288038000, 1288038300  
  2010-10-25 15:25:00, 2010-10-25 15:30:00, 1288038300, 1288038600  
  2010-10-25 15:30:00, 2010-10-25 15:35:00, 1288038600, 1288038900  
  2010-10-25 15:35:00, 2010-10-25 15:40:00, 1288038900, 1288039200  
  2010-10-25 15:40:00, 2010-10-25 15:45:00, 1288039200, 1288039500  
  2010-10-25 15:45:00, 2010-10-25 15:47:09, 1288039500, 1288039629  
  </code></pre>
  
  <p>If you're interested, here's the quick &amp; dirty on the app and why I need the data:<br>
  App overview: The application receives very simple POST requests generated by a basic sensor device when its input pins go to ground, which submits an INSERT query to the database where MySQL records a timestamp (as started). When the input pins return from a grounded state, the device submits a different POST request, which causes the PHP app to submit an UPDATE query, where a modification time timestamp is inserted (as ended). </p>
  
  <p>My employer recently changed the periodic reporting unit of measure from <em>Seconds "On" Per Day</em> to <em>Seconds "On" Per 5 Minute Interval</em>. I had formulated what I thought would be a workable solution, but when I looked at it on paper, it looked like Rube Goldberg's nightmare constructed in MySQL, so that was out.  </p>
  
  <p>Any suggestions as to how to break these spans into 5 minute blocks? Keeping it all in MySQL would be my preference, though I'll take <strong>any</strong> suggestions. Thank you for any suggestions you may have.</p>
  
  <p>Again, I apologize if this is a no-brainer.<br>
  If I ask any additional questions of the SO collective consciousness in the future, I'll try to word them a bit better. Any help will be happily welcomed.</p>
  
  <p>Thanks,<br>
  Neren</p>