MySQL Forums
Forum List  »  Newbie

downsampling or aggregating records for charting
Posted by: Dave C
Date: May 30, 2014 08:16AM

I have a table AuraUsers with StartTime, FinishTime and Completed. I do lots test to cull bad start/endtimes in the data, so that's why my query is so big. But ultimately, this is my question:

I have 11,000 records, which is too many to graph (using graph.js). How can I get a good representation of my data, while only graphing, say, 1000 points? Conceptually, I can think of two ways: just pull one in 10 records, or aggregrate every 10 records into one (more accurate). I'm not really picky about how accurate it is.

Below is my query, but pointing me at a standard solution would be all right too.

SELECT 
	TIME_TO_SEC(TIMEDIFF(FinishTime,StartTime)) 
	AS json 
	FROM AuraUsers 
	WHERE (
		StartTime IS NOT NULL 
		AND 
		FinishTime IS NOT NULL 
		AND 
		Completed=7 
		AND 
		TIME_TO_SEC(TIMEDIFF(FinishTime,StartTime)) > 0 
		AND 
		TIME_TO_SEC(TIMEDIFF(FinishTime,StartTime)) < 86400
	) 
	ORDER BY TIME_TO_SEC(TIMEDIFF(FinishTime,StartTime)) 

	) DESC 


Options: ReplyQuote


Subject
Written By
Posted
downsampling or aggregating records for charting
May 30, 2014 08:16AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.