STEP function in Select

[rossking]

I have a DB

 

It gets records from a sensor every 5 min

 

This is then displayed on a Chart

 

When I request 4 Hrs I get 48 records x 12 Sensors, Not a issue

 

When I request 12 Hrs I get 144 records x 12 Sensors, A little Slower but Still OK

 

But as I start to request longer periods Like 7 Days I start getting larger and Larger data sets causing time out issues with the AJAX Calls (4-30MB per sensor downloads of data)

 

 

 

 

When looking at 4 weeks I really cant see the fine detail so is there a way to request records from MYSQL that drops some records in between

 

Kinda like the Step function in a For loop

 

 

If I request a period that has 2048 records but only want 204 records is there a way to say give me From -> To dates Step 10 and only return every 10th Record in the data set

 

 

Thanks

[mac_gyver]

just off of the top of my head, you would do a GROUP BY some_time_interval to consolidate data for the time interval, getting an average, in the query, of the data for each time interval. it would be fairly easy to craft a query that either groups by - hour, day, week, month, or year intervals, depending on the scale you need, assuming you are storing a DATETIME value with each data point.

[Barand]

Using the MOD() function on a numeric value eg "id" or TO_SECONDS(rec_date) should allow you to select approximately every Nth record

 

... WHERE MOD(id,20) = 0.

mysql> SELECT * FROM test;
182685 rows in set (0.15 sec)

mysql> SELECT * FROM test WHERE MOD(id,10)=0;
18268 rows in set (0.08 sec)

Alternatively check why it takes a long time. 2048 recs is not a large dataset

 

If you are doing lots of AJAX calls you will be incurring the overhead of connecting to the DB each time. Is it possible to accomplish the task in a single call?

Edited September 15, 2014 by Barand