CODEWITHSUNDEEP
mysqlintervals
Jay Povey
Jay Povey

Reputation: 137

Anomaly in Mysql query

This is not my query, its a query that someone wrote that i am now working with.

I have a database like 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 is 12 out of about 200k rows)

This is my query

SELECT x.date, t.high, t.low, t.open, t.close, x.open_id, x.close_id from (SELECT MIN(`date`) as `date`, MAX(`close_id`) as `close_id`, MIN(`open_id`) as `open_id` 
FROM `AUDNZD_minutes` 
WHERE `date` >= '2011-03-07 00:00:00' and `date` < '2011-03-11 12:00:00' 
GROUP BY round(UNIX_TIMESTAMP(date) / 600) order by `date`) as x inner join `AUDNZD_minutes` as t on x.close_id = t.close_id

It is selecting rows from that data base in 10 minute intervals. However I always have this 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 that the diffrence between the first row and the second is 5 minutes and everythign after this is 10 minutes. this happens with any interval i 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 i correct this query?

Upvotes: 0

Views: 291

Answers (1)

Alex Blex
Alex Blex

Reputation: 37048

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

select  FROM_UNIXTIME(round(UNIX_TIMESTAMP('2009-05-01 00:04:00') / 600) *600) from dual;

results with 2009-05-01 00:00:00 and 

select  FROM_UNIXTIME(round(UNIX_TIMESTAMP('2009-05-01 00:06:00') / 600) *600) from dual;

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

Consider ceil or floor functions instead.

As a side note, @Strawberry made a point. Try to use anything like http://sqlfiddle.com/ to show some efforts in asking question at least.

Upvotes: 1

Related Questions