Daily Average=1440 parameter purpose defeated by results limits | ThingSpeak API | Forum

Avatar

Please consider registering
Guest

sp_LogInOut Log In sp_Registration Register

Register | Lost password?
Advanced Search

— Forum Scope —






— Match —





— Forum Options —





Minimum search word length is 3 characters - maximum search word length is 84 characters

sp_Feed sp_TopicIcon
Daily Average=1440 parameter purpose defeated by results limits
No permission to create posts
June 26, 2013
6:09 pm
Avatar
turgo

Gold
Members
Forum Posts: 77
Member Since:
June 16, 2013
sp_UserOfflineSmall Offline

When I do:
http://api.thingspeak.com/channels/9/feed.json?start=2011-11-11%2010:10:10&end=2012-11-11%2011:11:11&average=1440
to obtain a years worth of daily average values, only two points are returned. This is utterly worthless. Apparently, the 8000 point limit on results is for the raw data that the average function looks at (two days is about 12,000 points), and not the number of results it returns. This needs to be changes so that the average function is worth using, and so that users are not forced to download the entire data set in order to make a thumbnail snapshot of the historical data.

Currently, a big advantage of ThingSpeak over Xlively/Cosm/Pachtube is that the user has access to the entire data history, instead of just 30 days. However, we need effective API tools to access this data.

December 20, 2013
3:04 pm
Avatar
turgo

Gold
Members
Forum Posts: 77
Member Since:
June 16, 2013
sp_UserOfflineSmall Offline

I thought I'd bump this request for better functionality since IO bridge appears to be monitoring the forums this month. If you upgrade Thingspeak to provide 8000 points of AVERAGED data, I will post a Javascript HighStock webpage that allows a user to dynamically chart any time period for multiple fields. It's like a yahoo stock chart. It will also export that data as CSV.

December 21, 2013
11:45 pm
Avatar
lee

Admin
Forum Posts: 457
Member Since:
March 14, 2011
sp_UserOfflineSmall Offline

Hi,

Yes you are correct that the 8000 point limit applies to the raw underlying data. If we allowed an average of every 1440 data points for a full year, at maximum we'd have to first retrieve more than 2 million records from the database and then average every 1440 points. We aren't currently able to provide this functionality, since it would severely affect the performance of other users' data. Unfortunately we have to place limits somewhere.

One alternative that you could do is to retrieve the averaged data from our server or calculate it yourself, and then log this data back to ThingSpeak in another channel after every 1440 data points have been sent.

Another alternative would be to install the open source version of ThingSpeak ( https://github.com/other/thingspeak ) to your own server, since the rate limits would't apply.

lee

December 22, 2013
2:11 am
Avatar
turgo

Gold
Members
Forum Posts: 77
Member Since:
June 16, 2013
sp_UserOfflineSmall Offline

Hi Lee,

Thanks for the quick reply.

Please take a look at the kind of cool chart that averaging would allow:
http://www.highcharts.com/stock/demo/lazy-loading
Notice how you can dynamically zoom in to a time period of interest. It's all done with averages.

I think that a chart like this is the missing link to make the gigabytes of historical data you store truly useful. Currently there are no effective tools to access all that data. There is not enough time in the day to download a even year's worth of historical data 8000 points at a time. (I've tried it, after a months worth of data, and half an hour downloading, the server stops working.) Providing access to averaged data would allow a user to focus on the part of the historical record that they were interested in, and download only that part. Your server may have to access averages of a several million point history, but would not need to download the entire record. Additionally, the daily averages could be cashed server side for this function. Please give historical averages some consideration.

January 28, 2014
3:42 pm
Avatar
lee

Admin
Forum Posts: 457
Member Since:
March 14, 2011
sp_UserOfflineSmall Offline

Hi,

We've made some improvements to how daily calculations are made. The first time these calculations are made we store them in the database (it will take some time to crunch the data), but subsequent requests will load up the precalculated data. Your example of http://api.thingspeak.com/channels/9/feed.json?start=2011-11-11%2010:10:10&end=2012-11-11%2011:11:11&average=1440 should now work.

lee

January 29, 2014
1:59 am
Avatar
turgo

Gold
Members
Forum Posts: 77
Member Since:
June 16, 2013
sp_UserOfflineSmall Offline

Awesome, I'll see about changing the multichannel graphing web page to take advantage of this. http://community.thingspeak.com/forum/announcements/thingspeak-live-chart-multi-channel-second-axis-historical-data-csv-export/

Looking at the output:
{"created_at":"2011-11-28T00:00:00Z","field1":"360.6664926931106","field2":"48.91625186347463"}

I see lots of extra decimal places. Both storage and download could be hastened with some rounding.

January 29, 2014
12:43 pm
Avatar
lee

Admin
Forum Posts: 457
Member Since:
March 14, 2011
sp_UserOfflineSmall Offline

You're right, the download can have rounding applied which will make the response shorter in length: http://api.thingspeak.com/channels/9/feed.json?start=2011-11-11%2010:10:10&end=2012-11-11%2011:11:11&average=1440&round=2 . For storage, rounding the values wouldn't help since we're using a fixed database column size.

lee

February 10, 2014
1:49 am
Avatar
turgo

Gold
Members
Forum Posts: 77
Member Since:
June 16, 2013
sp_UserOfflineSmall Offline

I've started upgrading the multichannel historical data graphing tool, and see that you have only changed the 1440 parameter to look at more than 8000 records. To be useful, the other average periods need full access as well. However, I see that storing each set of values may become cumbersome.

It would be particularly helpful to add 1, 2, and 5 minute averages. Data recorded in 15 second intervals is great for live charts, but when viewing historical data slightly longer averages are more efficient. Would it be possible to add 1, 2, 5 and 10 minute averages with full access? Since these shorter averages would not require looking at most of the data set, the server performance may not be adversely affected. Thus, you would not have to store the averaged data. The change could be easy!

February 10, 2014
11:55 am
Avatar
lee

Admin
Forum Posts: 457
Member Since:
March 14, 2011
sp_UserOfflineSmall Offline

The 1, 2, and 5 minute averages won't be changed anytime soon, since we have to choose to make cutoffs (limit=8000) somewhere that won't adversely affect server performance. We are trying to maintain a proper balancing act between results length (which causes more table rows to be scanned) versus storing calculated data (which causes more database storage to be used), so that we can keep this service free and as efficient as possible.

lee

February 10, 2014
3:57 pm
Avatar
turgo

Gold
Members
Forum Posts: 77
Member Since:
June 16, 2013
sp_UserOfflineSmall Offline

Do you plan to store averages for any periods shorter than 1440 minutes?

Another way to look at this problem would be to store averages for 10 minute periods, and then calculate averages for periods greater than 10 minutes on the fly derived from the 10 minute data.

I wonder if server performance could be dramatically improved by adopting a float data type, instead of fixed width characters.

February 10, 2014
5:01 pm
Avatar
lee

Admin
Forum Posts: 457
Member Since:
March 14, 2011
sp_UserOfflineSmall Offline

We may implement 10 minute calculations in the future, but don't have any immediate plans for it.

Some of our users store character (and UTF8) data in the fields; changing it to floats would only affect the size of the database, but not the number of table rows that need to be scanned.

lee

February 11, 2014
12:59 am
Avatar
turgo

Gold
Members
Forum Posts: 77
Member Since:
June 16, 2013
sp_UserOfflineSmall Offline

Hi Lee,

Adding 10 minute stored averages would be great! With 10 minute averages, 8000 points would give a pretty detailed look at 55 days worth of 15 second data. That would make a historical data viewer fast and useful!

Currently it requires about 40 separate requests to see 55 days and takes half an hour to load, making it impractical to see historical data.

February 16, 2014
7:50 pm
Avatar
turgo

Gold
Members
Forum Posts: 77
Member Since:
June 16, 2013
sp_UserOfflineSmall Offline

I've been working with the daily average, and found that the first time I call for it, the server gives a 504 time out after a minute. This repeats five cycles, and then finally returns the Daily average. That's a huge delay.

February 17, 2014
12:47 pm
Avatar
lee

Admin
Forum Posts: 457
Member Since:
March 14, 2011
sp_UserOfflineSmall Offline

Hi,

We don't store the daily average data for every Channel. Once an initial request is made for daily calculated data, then we store that information for the Channel, so the initial request may take a while as all of the calculations have to be performed. Once this is finished however, the calculated data for those dates will never have to be recalculated, and there should be no performance problems.

lee

Forum Timezone: America/New_York

Most Users Ever Online: 166

Currently Online:
24 Guest(s)

Currently Browsing this Page:
1 Guest(s)

Top Posters:

rw950431: 272

Vinod: 240

piajola: 95

turgo: 70

vespapierre: 63

Adarsh_Murthy: 62

Member Stats:

Guest Posters: 1

Members: 8665

Moderators: 1

Admins: 2

Forum Stats:

Groups: 3

Forums: 14

Topics: 1600

Posts: 5760

Newest Members:

anis, 43994554AKS, suda, marisol, qkpham, SANGAMNATH B

Moderators: cstapels: 460

Administrators: Hans: 405, lee: 457