intervals - Anomaly in Mysql query -

this not query, query wrote working with.

i have database so

id  date                high        low         open        close       open_id     close_id  1   2009-05-01 00:00:00 0.729125    0.729225    0.72889     0.72889     1           74 2   2009-05-01 00:01:00 0.72888     0.728895    0.72883     0.72887     75          98 3   2009-05-01 00:02:00 0.728865    0.72889     0.72881     0.72888     99          121 4   2009-05-01 00:03:00 0.72891     0.72901     0.72891     0.729       122         141 5   2009-05-01 00:04:00 0.728975    0.729115    0.728745    0.72878     142         225 6   2009-05-01 00:05:00 0.728785    0.72882     0.72867     0.72882     226         271 7   2009-05-01 00:06:00 0.72884     0.72887     0.728735    0.728785    272         293 8   2009-05-01 00:07:00 0.728775    0.728835    0.72871     0.728835    294         317 9   2009-05-01 00:08:00 0.728825    0.72899     0.728795    0.72897     318         338 10  2009-05-01 00:09:00 0.72898     0.729255    0.72898     0.72922     339         383 11  2009-05-01 00:10:00 0.72922     0.729325    0.72908     0.729105    384         437 12  2009-05-01 00:11:00 0.729115    0.72918     0.728635    0.72905     438         553 

(this 12 out of 200k rows)

this query

select x.date, t.high, t.low, t.open, t.close, x.open_id, x.close_id (select min(`date`) `date`, max(`close_id`) `close_id`, min(`open_id`) `open_id`  `audnzd_minutes`  `date` >= '2011-03-07 00:00:00' , `date` < '2011-03-11 12:00:00'  group round(unix_timestamp(date) / 600) order `date`) x inner join `audnzd_minutes` t on x.close_id = t.close_id 

it selecting rows data base in 10 minute intervals. have anomaly.

2011-03-07 00:00:00 1.3761      1.375595    1.375815    1.37589     55180489    55181083 2011-03-07 00:05:00 1.376055    1.37568     1.375925    1.37594     55181084    55181751 2011-03-07 00:15:00 1.37609     1.375835    1.375835    1.37606     55181752    55182003 2011-03-07 00:25:00 1.37578     1.37526     1.375505    1.375555    55182004    55182615 2011-03-07 00:35:00 1.374645    1.374455    1.374535    1.374645    55182616    55183178 2011-03-07 00:45:00 1.37463     1.373775    1.374085    1.374025    55183179    55183820 

you can see diffrence between first row , second 5 minutes , everythign after 10 minutes. happens interval try.

for example, 20 miunte intervals

2011-03-07 00:00:00 1.376155    1.375915    1.37594     1.376025    55180489    55181434 2011-03-07 00:10:00 1.376105    1.37592     1.37593     1.376085    55181435    55182273 2011-03-07 00:30:00 1.374025    1.37388     1.373965    1.37401     55182274    55183429 2011-03-07 00:50:00 1.373895    1.373595    1.37365     1.373595    55183430    55184894 2011-03-07 01:10:00 1.37382     1.373505    1.37373     1.373715    55184895    55185885 2011-03-07 01:30:00 1.373305    1.373025    1.373265    1.373055    55185886    55187306 

how can correct query?

round function rounds numbers using basic math rules learned in primary:

select  from_unixtime(round(unix_timestamp('2009-05-01 00:04:00') / 600) *600) dual; 

results 2009-05-01 00:00:00 ,

select  from_unixtime(round(unix_timestamp('2009-05-01 00:06:00') / 600) *600) dual; 

results 2009-05-01 00:10:00, (on provided dataset) have half of interval in first line if keep using it.

consider ceil or floor functions instead.

as side note, @strawberry made point. try use http://sqlfiddle.com/ show efforts in asking question @ least.