Cumulative values between 2 times
Posted: Wed Nov 17, 2010 5:34 pm
I have an interesting problem and im not sure what the most efficient way of doing this would be.
I have a table of data set out like the following:
[text]
node_id | poll_stamp | value
---------+------------------------+--------
77 | 2010-06-07 21:00:00+00 | 33.883
77 | 2010-06-07 21:15:00+00 | 35.244
77 | 2010-06-07 21:30:00+00 | 37.118[/text]
Data is entered into this table every 15 minutes, 24 hours a day, each recording a common node_id unique to the place the data is coming from.
From this data, i am generating a line graph using ZingChart.
There are 2 individual problems of the same major problem that i am facing.
1. Read cumulative values between 9am (day1) and 8:59am (day2). So from 9am, read cumulative values for the entire day, and then once 9am the following day comes around, reset the cumulative value.
2. The data which is being stored cannot be modified in any way, however because of the large amount of data being stored and accessed, it needs to be somewhat efficient and needs to minimize load.
The problem in trying to satisfy requirement #2, means that i cant run a query on each data point and select the sum up until that datetime.
This leaves me with the option of selecting all the data, then applying the changes as a PHP script.
I'm open to suggestions on how to get around this problem.
cheers.
Weiry
I have a table of data set out like the following:
[text]
node_id | poll_stamp | value
---------+------------------------+--------
77 | 2010-06-07 21:00:00+00 | 33.883
77 | 2010-06-07 21:15:00+00 | 35.244
77 | 2010-06-07 21:30:00+00 | 37.118[/text]
Data is entered into this table every 15 minutes, 24 hours a day, each recording a common node_id unique to the place the data is coming from.
From this data, i am generating a line graph using ZingChart.
There are 2 individual problems of the same major problem that i am facing.
1. Read cumulative values between 9am (day1) and 8:59am (day2). So from 9am, read cumulative values for the entire day, and then once 9am the following day comes around, reset the cumulative value.
2. The data which is being stored cannot be modified in any way, however because of the large amount of data being stored and accessed, it needs to be somewhat efficient and needs to minimize load.
The problem in trying to satisfy requirement #2, means that i cant run a query on each data point and select the sum up until that datetime.
This leaves me with the option of selecting all the data, then applying the changes as a PHP script.
I'm open to suggestions on how to get around this problem.
cheers.
Weiry