database - Select equally distant rows based on a timestamp column in MySQL -

how can select fixed number of rows table, in timestamp column, , return rows equally distant each other. need these points sample points use plot timeseries. aware of other techniques solve problem such crossfilter, etc, want able using server, now.

for example, table below: (timestamp simplified clarity)

id    key    val   timestamp 1     'a'    100   1am 2     'b'    120   2am 3     'c'    130   3am 4     'd'    140   4am 5     'e'    130   5am 6     'f'    135   6am 7     'g'    136   7am 8     'h'    139   8am 9     'i'    149   9am 10    'j'    140   10am 11    'k'    140   11am 12    'l'    135   12pm 

so want able run query return sample of size 3, example, , should return rows 1, 5 , 9.

i dont want use id, because table more complicated this, , applying clauses, etc query, , using id not going work.

from working other rdbss aware of rank, doesnot seem exists in mysql, saw workarounds, 1 here, don't think clean way write mysql.

any suggestions on how approach problem?

you need step function map time stamps finite set of "steps". expressed in mysql:

-- -- `min_v` , `max_v` respectively first , last value value on range -- `samples` number of sample ("steps") expected -- `value` actual value --  create function step(min_v int, max_v int, samples int, value int) returns int deterministic return min_v + (value - min_v) * (samples - 1) div (max_v-min_v) 

for sake of simplicity, used here integers instead of timestamps. find in mysql documentation how convert timestamps "unix epoch".

once function defined, have group "step" in select query, keeping first sample of each step:

select data.k, data.value tbl data join (select id, min(ts) tbl group step(1,12,4,ts) ) s on s.id = data.id; 

see http://sqlfiddle.com/#!2/d5ccb/3 live example.