beginner help with lazy loading of JSON data for (large) graph data

[radiance32]

Hi everybody,

I'm new here, just signed up, and am %100 new to highcharts...

I don't have much experience with javascript nor JSON,
but I do have PHP/MySQL development experience....

I need some help getting started, i've consulted the documentation and it's just really hard for me to find what i need...

If you look at this graph, it's one of the highcharts-stock demos that loads the data it needs from the server via a JSON query (I suppose),
and it only loads what it needs to show the graph of the selected time-range, as the total data set on the server is, according
to the link on the left 1.7 million points, so it's loaded on an as needed, asyncronous basis.

The data url is in the top of the code:
const dataURL = 'https://demo-live-data.highcharts.com/a ... rical.json';


And the code that reloads the data whenever the chart's view is changed by the user is (i assume) here:

function afterSetExtremes(e) {
const { chart } = e.target;
chart.showLoading('Loading data from server...');
fetch(`${dataURL}?start=${Math.round(e.min)}&end=${Math.round(e.max)}`)
.then(res => res.ok && res.json())
.then(data => {
chart.series[0].setData(data);
chart.hideLoading();
}).catch(error => console.error(error.message));
}


So, I assume it composes a HTTP GET request like: GET https://demo-live-data.highcharts.com/a ... (something???)

What I would like to do is to use the highchart-stock as is, it looks good and does everything I want it to do,
I've already integrated it into my site and adjusted the look/colours etc,
but, offcourse, I want it to load data from my MySQL database which holds many millions of key/value pairs (a datetime with microseconds of precision, and a floating point value),
in a large mysql table.

So, I will need to write some PHP code that does the SQL query on my database (a select that returns only what the chart needs with it's "start" and "end" values) to get the key/value pairs that the highcharts-stock graph needs at that moment via this JSON query,
and then generates a JSON content type return data that contains the correct JSON pairs / syntax etc... for the chart to update it's graph.
I've no idea what this all looks like.

If you look at my "So, I assume it composes a HTTP GET request like:" above,
what are the (something???), I assume these are beginning and end dates, but i've no idea what format they are, eg, are they unix timestamps, or some kind of formatted date/time string ?

If somebody could explain what the "start" and "end" values in that GET request would be, just an example so i know the syntax and I can build my own JSON server that understands the begin and end values,
and, if somebody also could show me an example of what I should return, eg how are the key/value pairs named or what is their syntax ?

I have NEVER written any javascript before, however i'm a proficient C/C++ programmer and used to program PHP for a living a long time ago, so, with some answers to these questions from someone who
knows what to do, I'm sure I can get this working...
So please if you explain, try to explain thing verbosely, as I do not know much about javascript at all, nor much about highcharts...

So please, if anybody can explain and shed some light on this,
I can hopefully get this going and have the highcharts-stock display my own data from my mysql database...

Thank you,
Terrence

[radiance32]

At least for a start (although i have other questions in my main post above), if someone could explain me the format the dates for the "start" and "end" variables that get given to the HTTP GET request are, I'll have something to start working with...

[radiance32]

I wrote a little json server that get's the "start" and "end" values from the GET request,
and they appear to be unix epoch timestamps, although in some cases I get weird values.

Can anyone shed some light as to what kind of data this is ? I suppose it's some kind of date value,
probably a unix epoch timestamp or similar ?

I also had a look at the output of requesting the provided 1.7 million point data JSON URL:


and it returns a large amount of values in this format:
[[883609200000,3.41,5.01,3.37,4.58,350377440],[886287600000,4.62,5.97,4.34,5.91,246714750], etc...

I don't know how many of these blocks it returns, but i'd say a about 200 or so, probably enough to display a chart of the whole dataset, but just enough points that are required to create the chart, as sending all 1.7 million points would be way too much data...

I'm just speculating, but I assume each block (for example: [883609200000,3.41,5.01,3.37,4.58,350377440]) contains 4 values of the x axis, and the 2 values at the left and right are some kind of start and end time values for the 4 data points in question. Since the leftmost one is much larger than the rightmost one, I assume the leftmost one is the start timestamp, and the rightmost one is just the duration of the 4 points ? or some similar value ?

It would be great if someone can shed some light on this, so i can finish building my own SQL driven JSON data server to send my data into the highcharts-stock chart.

Thank you,
Terrence

[dominik.c]

Hello radiance32!

Welcome to our forum and thanks for contacting us with your question!

We can check how these 5 values are used in the chart. By console.logging the chart in load event and looking through chart.series[0].data. Then in options, we can see that only the first four values are used.

Demo: https://jsfiddle.net/BlackLabel/wktmLrf8/

When we check the API of the candlestick series (this is the series that is used in the demo with 1.7 million points) we can see that there are two ways of how the points can be provided to the chart. Check it out here: https://api.highcharts.com/highstock/se ... stick.data (Neither of them requires 5 values)

We can say that in this demo the last value is irrelevant. Do you want to use the candlestick series in your project too? There are many series that require different data. E.g in the line series you only need one x value and one y value.

Feel free to ask any further questions, I'll do my best to help you.

Best regards!

[radiance32]

Hello radiance32!

Welcome to our forum and thanks for contacting us with your question!

We can check how these 5 values are used in the chart. By console.logging the chart in load event and looking through chart.series[0].data. Then in options, we can see that only the first four values are used.

Demo: https://jsfiddle.net/BlackLabel/wktmLrf8/

When we check the API of the candlestick series (this is the series that is used in the demo with 1.7 million points) we can see that there are two ways of how the points can be provided to the chart. Check it out here: https://api.highcharts.com/highstock/se ... stick.data (Neither of them requires 5 values)

We can say that in this demo the last value is irrelevant. Do you want to use the candlestick series in your project too? There are many series that require different data. E.g in the line series you only need one x value and one y value.

Feel free to ask any further questions, I'll do my best to help you.

Best regards!

Hi Dominik,

Thanks for replying to my post, I really appreciate it

Unfortunately, it doesn't help me much, I don't have any experience with javascript, so things like:
"We can check how these 5 values are used in the chart. By console.logging the chart in load event and looking through chart.series[0].data. Then in options, we can see that only the first four values are used." are useless as I've no clue how to do that.

I'm currently evaluating highcharts-stock as it seems to be the best chart solution for our product,
and we're interested in purchasing a commercial license, if we can get it to work.
Unfortunately I only have C/C++, PHP, SQL and HTML/CSS experience, and javascript is pretty obscure to me...

Can you just maybe guide me through the process, what I'm trying to achieve is fairly simple:

I have a table in my MySQL database, that contains millions of date / value pairs. eg (one row for example: 2012-07-17 15:32:21:687, 42.640)
I'd like to have the highcharts-stock display it in a similar fashion as the 1.7 million points demo, eg, loading the data as needed, only what's needed, based on the current view (positions, zoom) of the chart.
I don't need candlestick data, just a line or spline is perfect.

So, if you could just tell me what JSON data I need to generate from my JSON PHP server, and what changes I need to make to the javascript code for highcharts-stock (if needed at all), I should be able to get this going.

I assume I'd have to send a JSON array with only 2 values as you said a "line" type chart only needs 2 values (eg x and y)
So something like this (but with much more data points offcourse):

[ [<datetime>,42.67],[<datetime>,36.546],[<datetime>,27.54],[<datetime>,51.42] ]

My questions are:

* What format should I use for the <datetime> in my example JSON data above?
What format does the highcharts-stock code use for a date & time value in milliseconds ? (eg 2012-07-17 15:32:21:687)
The milliseconds resolution is important as my data contains sample values that can have, sometimes, have 100 samples per second or so...
The example supplied with the values like these "[883609200000,3.41,5.01,3.37,4.58,350377440]", if I examine them, to me it looks like the first value (883609200000) is some sort of date & time value. It looks similar to a UNIX Timestamp (eg number of seconds or milliseconds since the epoch (1/1/1970)

* What should my JSON server expect to receive from the HTTP GET variables names "start" and "end" ?
This is what the chart requests from the JSON server whenever it needs to load new data when it's view/zoom/range has been changed by the user.
I assume just <datetime> for the left (start) and the <datetime> for the <right> of the chart.
The format of this <datetime> would be the same as the one above for the x values of the chart data ?

If somebody could explain this to me,
I'm sure I can get it working...

Thanks,
Terrence

[dominik.c]

Hi again!

1. Exactly, the best datetime format would be to use UNIX Timestamp (number of seconds since the epoch 1/1/1970) as you said.

2. Yes, the format would be the same and these variables are responsible for the time period from left to right.

I'm not sure what else I can do to help you. Generally, on the forum, we work on some piece of code that doesn't work and try to fix it. If you have any specific questions about something that doesn't work for you feel free to ask about it too.

Best regards!

[radiance32]

Hi again!

1. Exactly, the best datetime format would be to use UNIX Timestamp (number of seconds since the epoch 1/1/1970) as you said.

2. Yes, the format would be the same and these variables are responsible for the time period from left to right.

I'm not sure what else I can do to help you. Generally, on the forum, we work on some piece of code that doesn't work and try to fix it. If you have any specific questions about something that doesn't work for you feel free to ask about it too.

Best regards!

Hi Dominik,

Your 2 answers are exactly what I needed,
and I've managed to get my PHP json server coded and its working great now with the "1.7million candlestick demo" code, slightly modified to change the json source and the title, subtitles and I made it into a line or spline type graph with just the 2 values (timestamp, value) for the x and y axii.

So this issue is solved, thank you very much for explaining it to me

Cheers,
Terrence

PS: The other only remaining problem is my other post here: viewtopic.php?f=12&t=46325
Which I hope we can get working after that is done, my app would be ready

[dominik.c]

Hi again!

I'm glad I could help you and it's great that you made it work, congrats!

Good luck with further development.
Best regards!