MySQL Split Time Ranges into Smaller Chunks
- by Neren
Hello all,
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.
Problem in a Nutshell:
I have a MySQL table full of start and end datetime (GMT -5) & 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.
Data Example:
started, ended, started_UNIX, ended_UNIX
2010-10-25 15:12:33, 2010-10-25 15:47:09, 1288033953, 1288036029
What I'm hoping to get:
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
If you're interested, here's the quick & dirty on the app and why I need the data:
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).
My employer recently changed the periodic reporting unit of measure from Seconds "On" Per Day to Seconds "On" Per 5 Minute Interval. 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.
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 any suggestions. Thank you for any suggestions you may have.
Again, I apologize if this is a no-brainer.
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.
Thanks,
Neren
