CODEWITHSUNDEEP
phpmysqltime
Neren
Neren

Reputation: 1

MySQL Split Time Ranges into Smaller Chunks

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

Upvotes: 0

Views: 1940

Answers (2)

dnagirl
dnagirl

Reputation: 20456

This is similar to a "blank line" problem and can be addressed with a number table if you really must change your data.

In your db create a table called numbers and insert the numbers 0 through 9:

CREATE TABLE  numbers (
   num int(10) unsigned NOT NULL,
   PRIMARY KEY (num)
 );

The numbers table can be used for making sequences of integers which can be transformed into times. For instance, to get a sequence from 15:15 to 15:45:

   SELECT ADDDATE('2010-10-25 15:15:00', INTERVAL 5*`i` MINUTE) FROM (
       SELECT 10*n1.num + n2.num AS i
        FROM numbers n1 CROSS JOIN numbers n2) nums
     WHERE i <= TIME_TO_SEC(TIMEDIFF('15:45', '15:15'))/(5*60)
     ORDER BY i ASC;

Make the data in 2 steps. First, create the missing rows. Then update the original row to be 5 minutes long or less.

Upvotes: 0

wallyk
wallyk

Reputation: 57774

Instead of trying to transform the raw data, which is well and densely represented, write a retrieval module which reads the raw and internally transforms it to the desired representation. I suppose it could write it back as a new table, but is there any reason the reporting could not be generated directly from the program?

Upvotes: 1

Related Questions