CODEWITHSUNDEEP
mysqlsql
WIESIEK
WIESIEK

Reputation: 31

how to select first and last value of rows and between 5 minutes interval

I hava a table with sample forex one minute bar quotes. 

id,quote_name,quote_date,quote_time,open_rate,close_rate,high_rate,low_rate
"1417","EURUSD","2015-01-01","13:01:00","1.2096","1.2096","1.2097","1.2096"
"1418","EURUSD","2015-01-01","13:02:00","1.2097","1.2096","1.2097","1.2096"
"1419","EURUSD","2015-01-01","13:04:00","1.2096","1.2098","1.2101","1.2096"
"1420","EURUSD","2015-01-01","13:05:00","1.2099","1.2099","1.2099","1.2099"

Is it possible to create select statement which will be return 5 minute interval quotes. I mean that it should select 5 rows between each 5 minut interval and return open_rate from first row, close_rate from last quote, and min and max of high_rate and low_rate. Is it possible at all? How to do that.

What I know is that how to select min and max value between two dates.

Upvotes: 1

Views: 223

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Getting the five-minute interval is a bit of a pain. One way is to convert to seconds and divide by 300. Then, getting the first and last is also tricky. In this case, I would suggest a trick using substring_index() and group_concat():

select quote_date, min(open_time) as open_time,
       substring_index(group_concat(open_rate order by quote_time), ',', 1) as first_open,
       substring_index(group_concat(close_rate order by quote_time desc), ',', 1) as last_close,
       min(high_rate), max(high_rate),
       min(low_rate), max(low_rate)
from quotes
group by quote_date, floor(to_seconds(quote_time) / 300);

Upvotes: 1

Related Questions