arrays - Efficient way to sum measurements / time series by given interval in php -

arrays - Efficient way to sum measurements / time series by given interval in php -

i have series of measurement info / time series in same interval of 15 minutes. furthermore, have given period (e.g. 1 day, current week, month, year, (...) , need summarize values hour, day, month, (...).

e.g. summarize values of lastly month, day.

my approach generate temporary array needed interval per period in first step. e.g. here in php (php not necessary, prefer python or javascript if provides faster method)

$this->temparray = array( '2014-10-01t00:00:00+0100' => array(), '2014-10-02t00:00:00+0100' => array(), '2014-10-03t00:00:00+0100' => array(), '2014-10-04t00:00:00+0100' => array(), (...) '2014-10-31t00:00:00+0100' => array() );

in sec step, loop through each date/value pair (in illustration 4*24*31, (96 per day)) , assign them temporary array. each date, override values datetime object. in illustration hr , minutes match keys in temp array.

$insert = array( 'datetime' => $datetime, 'value' => $value ); if ($interval == "d") { $this->temparray[date('y-m-d\t00:00:so', $datetime)][] = $insert; }

at lastly step, loop through temp array , summarize each array. result, receive array 31 new date/values pairs, summarized each day. works fine. there faster way or more efficient way? takes 0.5 seconds approach 1 month. (if interested in source code, add together gist). info stored within mysql database 15 mio entries.

// edit: think best way grouping mysql.

my current sql query fetch info 1 year:

select from_unixtime(pointoftime)) `date`, value info energymeterid="0ca64479-bddf-4b91-9e35-bf81f4bfa84c" , pointoftime >= unix_timestamp('2013-01-01t00:00:00') , pointoftime <= unix_timestamp('2013-12-31t23:45:00') order `date` asc;

if info lies in mysql, implement solution. trivial utilize various mysql date/time functions aggregate data. let's take simplistic illustration assuming table construction this:

id: autoincrement primary key your_datetime: datetime or timestamp field the_data: info items trying summarize

a query summarize day (most recent first) this:

select date(your_datetime) `day`, sum(the_data) `data_sum` table grouping `day` order `day` desc

if wanted limit period of time (last 7 days example) can add together condition

select date(your_datetime) `day`, sum(the_data) `data_sum` table your_datetime > date_sub(current_date(), interval 7 day) grouping `day` order `day` desc

here illustration specify range of datetimes

select date(your_datetime) `day`, sum(the_data) `data_sum` table your_datetime between '2014-08-01 00:00:00' , '2014-08-31 23:59:59' grouping `day` order `day` desc

sum hour:

select date(your_datetime) `day`, hour(your_datetime) `hour` sum(the_data) `data_sum` table your_datetime between '2014-08-01 00:00:00' , '2014-08-31 23:59:59' grouping `day`, `hour` order `day` desc, `hour` desc

sum month:

select year(your_datetime) `year`, month(your_datetime) `month` sum(the_data) `data_sum` table grouping `year`, `month` order `year` desc, `month` desc

here reference mysql date/time functions:

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-sub

php arrays datetime summarization