MS SQL Stored Proc - Query Help

[Tjoker]

Good day,

I have candle chart data from Bitmex that is supplied as 1min, 5min, 1hour and 1day intervals. I have created a Stored Proc to handle data roll up to other time frames i need, but I am sure there is quicker way of doing this directly on database.

I posted in detail on StackOverFlow:

MS SQL - Chart Data - Rolling up data

I have some exchange data from Bitmex that I pull via API and WebSocket and store in a SQL Database. I have created a table called Candles. I created columns from the XML for what I need. Bitmex ca...

stackoverflow.com

Any help will be much appreciated.

 

[bchip]

Any help will be much appreciated.

How do you get the data in?
Is it for historic data or live streaming?

C# and Python (especially python) has an incredibly quick and easy converting function.

 

[Tjoker]

How do you get the data in?
Is it for historic data or live streaming?

C# and Python (especially python) has an incredibly quick and easy converting function.

I use a Windows Service written in Visual Studio to pull the data from Bitmex's API here : https://www.bitmex.com/api/explorer/#!/Trade/Trade_getBucketed

I then store the xml files locally(basically caching it), and import into DB. The next time the service start, it will read everything it can from XML files, and fetch from API whatever is short, and storing that again locally.

To import the data from the XML to DB, is super quick, just under 2min.

The tricky part, as per my post, it to now roll up the source data, into the different time frames I need. From what I have been reaching, best is to do this on the DB side.

EDIT: And for LIVE data, I use WebSockets after I have all the data above, and I am in sync with Bitmex.

 

[Kosmik]

Have you tried group by? e.g.
GROUP BY DATEPART(HOUR, yourdatecolumn)

You should also be able to group by minute, and then just multiply that by that by an interval (lets say 5 or 10 minutes)

That would be the easy way to do it. Have a parameter on your store procedure for the interval you need and modify your query to suite. For example:

exec proc_SummiseData @Start='2020-04-30 13:00',@End='2020-04-30 17:00',@Interval='mm'

The idea being that you pass a range of data for flexibility and then the interval to break up that range into summed groups. You can then query on top of that or add another parameter for groups by 5min, 10min , etc. You could do further grouping by doing a modulas calc on the time portion ie: every 5 minutes would be mod(time,5)=0 or something similar, not sure , just shooting ideas.

 

[animal531]

If the fastest it runs is once per minute then I'd just do it with code.