Retrieve historical data

Question

I have a database with records and I need to make a market price history of the last 10 days and I return the price of open and the price of close.

The database is something:

ID | MARKET | EXCHANGE | LAST_TRADE | TIMESTAMP
1  |   1    |    1     |     25     | 1392562800 #2014/2/16 @ 15:0:0 <-- "Today's Open" (first insert of the day)
2  |   1    |    1     |     35     | 1392573600 #2014/2/16 @ 18:0:0
3  |   1    |    1     |     45     | 1392584400 #2014/2/16 @ 21:0:0
4  |   1    |    1     |     55     | 1392562800 #2014/2/16 @ 23:59:0 <-- "Today's Close" (last insert of the day)

Suppose we have more days, we have to group it by days and return the open and close price that day where the market and exchange = 1

Example:

ID | MARKET | EXCHANGE |    DATE   | OPEN | CLOSE
 1 |   1    |    1     | 2014/2/16 |  25  |  55

What format has this timestamp column? Is there really this string inside? — idmean, Feb 16 '14 at 12:30
The timestamp column is int(11), the string is a simple reference comment — Bayer, Feb 16 '14 at 12:36
can you please share the schema generation script so that I can check the query at my end before I pass it to you... — dopeddude, Feb 16 '14 at 12:45

score 2 · Accepted Answer · edited May 23 '17 at 12:29

2

After converting the timestamp and grouping by the date only, you can use this trick to get the first and last rows in the aggregate:

select 
  market, 
  exchange, 
  date(FROM_UNIXTIME(timestamp)),
  SUBSTRING_INDEX( GROUP_CONCAT(CAST(lastTrade AS CHAR) ORDER BY timestamp), 
         ',', 1 ) as open,
  SUBSTRING_INDEX( GROUP_CONCAT(CAST(lastTrade AS CHAR) ORDER BY timestamp DESC), 
         ',', 1 ) as close
from historicdata 
group by market, exchange, date(FROM_UNIXTIME(timestamp));

Fiddle here

It looks like your timestamp comments don't quite match the actual timestamps - row 1 and 4 both have timestamp 1392562800. The fiddle also includes an intraday high higher than the close, just as a test case :)

edited May 23 '17 at 12:29

Community

1
1

answered Feb 16 '14 at 13:18

StuartLC

104,537
17
209
285

1

Awesome!! great :) +1 – Bayer Feb 16 '14 at 13:28
Obviously bringing the ID column doesn't really make sense in the grouped result, although you could apply another aggregate to it, e.g. `MIN` or `MAX` – StuartLC Feb 16 '14 at 13:31

Retrieve historical data

1 Answers1