unix_timestamp() & retrieving historical rate data

I am using mysql to receive stock market rate data, and I have a rate
feed which tells me when the rate has changed. I input the data as it
comes in, into a mysql database that has both a unix_timestamp() field
and the rate field. Neither field is indexed.

What I need to do is calculate the Moving Average by taking the rate
from 30 seconds, 60, 90, etc seconds ago. Currently I am using the
humungous sub query select statement below. Are there any alternatives?
Any help would be greatly appreciated.

Matt Fong


select ((select bid from eurusd WHERE timestamp<=unix_timestamp() ORDER
BY timestamp DESC LIMIT 1)
+ (select bid from eurusd WHERE timestamp<=unix_timestamp()-30 ORDER
BY timestamp DESC LIMIT 1)
+ (select bid from eurusd WHERE timestamp<=unix_timestamp()-60 ORDER
BY timestamp DESC LIMIT 1)
+ (select bid from eurusd WHERE timestamp<=unix_timestamp()-90 ORDER
BY timestamp DESC LIMIT 1)
+ (select bid from eurusd WHERE timestamp<=unix_timestamp()-120 ORDER
BY timestamp DESC LIMIT 1)
+ (select bid from eurusd WHERE timestamp<=unix_timestamp()-150 ORDER
BY timestamp DESC LIMIT 1)
+ (select bid from eurusd WHERE timestamp<=unix_timestamp()-180 ORDER
BY timestamp DESC LIMIT 1)
+ (select bid from eurusd WHERE timestamp<=unix_timestamp()-210 ORDER
BY timestamp DESC LIMIT 1)
+ (select bid from eurusd WHERE timestamp<=unix_timestamp()-240 ORDER
BY timestamp DESC LIMIT 1)
+ (select bid from eurusd WHERE timestamp<=unix_timestamp()-270 ORDER
BY timestamp DESC LIMIT 1)
+ (select bid from eurusd WHERE timestamp<=unix_timestamp()-300 ORDER
BY timestamp DESC LIMIT 1)
+ (select bid from eurusd WHERE timestamp<=unix_timestamp()-330 ORDER
BY timestamp DESC LIMIT 1)
+ (select bid from eurusd WHERE timestamp<=unix_timestamp()-360 ORDER
BY timestamp DESC LIMIT 1)
+ (select bid from eurusd WHERE timestamp<=unix_timestamp()-390 ORDER
BY timestamp DESC LIMIT 1)
+ (select bid from eurusd WHERE timestamp<=unix_timestamp()-420 ORDER
BY timestamp DESC LIMIT 1)
+ (select bid from eurusd WHERE timestamp<=unix_timestamp()-450 ORDER
BY timestamp DESC LIMIT 1)
+ (select bid from eurusd WHERE timestamp<=unix_timestamp()-480 ORDER
BY timestamp DESC LIMIT 1)
+ (select bid from eurusd WHERE timestamp<=unix_timestamp()-510 ORDER
BY timestamp DESC LIMIT 1)
+ (select bid from eurusd WHERE timestamp<=unix_timestamp()-540 ORDER
BY timestamp DESC LIMIT 1)
+ (select bid from eurusd WHERE timestamp<=unix_timestamp()-570 ORDER
BY timestamp DESC LIMIT 1)
+ (select bid from eurusd WHERE timestamp<=unix_timestamp()-600 ORDER
BY timestamp DESC LIMIT 1)
+ (select bid from eurusd WHERE timestamp<=unix_timestamp()-630 ORDER
BY timestamp DESC LIMIT 1)
+ (select bid from eurusd WHERE timestamp<=unix_timestamp()-660 ORDER
BY timestamp DESC LIMIT 1)
+ (select bid from eurusd WHERE timestamp<=unix_timestamp()-690 ORDER
BY timestamp DESC LIMIT 1)
+ (select bid from eurusd WHERE timestamp<=unix_timestamp()-720 ORDER
BY timestamp DESC LIMIT 1)
+ (select bid from eurusd WHERE timestamp<=unix_timestamp()-750 ORDER
BY timestamp DESC LIMIT 1)
+ (select bid from eurusd WHERE timestamp<=unix_timestamp()-780 ORDER
BY timestamp DESC LIMIT 1)
+ (select bid from eurusd WHERE timestamp<=unix_timestamp()-810 ORDER
BY timestamp DESC LIMIT 1)
+ (select bid from eurusd WHERE timestamp<=unix_timestamp()-840 ORDER
BY timestamp DESC LIMIT 1)
+ (select bid from eurusd WHERE timestamp<=unix_timestamp()-870 ORDER
BY timestamp DESC LIMIT 1)) / 30 as a;

Re: unix_timestamp() & retrieving historical rate data

>I am using mysql to receive stock market rate data, and I have a rate
>feed which tells me when the rate has changed. I input the data as it
>comes in, into a mysql database that has both a unix_timestamp() field
>and the rate field. Neither field is indexed.

It seems to me you could get a big performance boost indexing the
timestamp field.

>What I need to do is calculate the Moving Average by taking the rate
>from 30 seconds, 60, 90, etc seconds ago. Currently I am using the
>humungous sub query select statement below. Are there any alternatives?
>Any help would be greatly appreciated.

Depending on how you need to handle missing or duplicate reports, you
might be able to do:

select avg(bid) from eurusd where timestamp between unix_timestamp() -870
and unix_timestamp();

This presumes you've got a report coming in about every 30 seconds, and
from your description, I'm not sure that's your setup.

Gordon L. Burditt

Re: unix_timestamp() & retrieving historical rate data

Gordon,

You are correct, there is no guarantee I get a quote every 30 seconds,
it can come in 3 times a second or only once every 60 seconds, I just
need a way to get the bid at any second of the day.