RaraituL RaraituL - 3 months ago 14
MySQL Question

How to collect chart data

I know this sounds like a stupid question, but what is the process of collecting chart data? how it should be saved into DB?

For example, i have a tickets application, and i want to display a graphic chart to show how many tickets have been created in the last 30minutes, 1hour, 12hours, 24hours

How should i store this data in the database, to be able to use it in the interface later on?

My idea would be to create a

TicketsSummary
table, and for each ticket created, create a new record in the Db, with the
dateTime
created

Afterwards, in the ui, based on the filter (lets say last 30minutes), i pool from the database all the tickets where
CreatedDateTime >= (DateTime.UtcNow - TimeSpan.AddMinutes(-30))


To make the interface up-to-date, i will pool every 60 seconds the data, and update the Chart.

Is this the correct approach?

Id CreatedDateTime Source
--------------------------------------------------
1 2017-04-13 05:17:29 Chat
2 2017-04-13 05:21:05 Email
3 2017-04-13 06:18:47 Chat
4 2017-04-13 06:49:10 Call Center


enter image description here

Answer

In general a 2d chart is just an association of x values and their corresponding mapping to y values. In your case if you will basically be charting the result of a SELECT DATE(CreatedDateTime) as X, COUNT(*) as Y FROM TicketsSummary GROUP BY X (where X and Y are the values you need to display.

If you want this to update dynamically you can use Highcharts that support this out of the box (sort of).

Basically you would need something like the following example (slightly modified here). http://jsfiddle.net/w15e9chz/

The idea is you generate the graph data initially and when the chart is loaded (in events: load) you can set an interval for a function to trigger every 60 seconds to do another AJAX call to your serverside and update the chart data.

Example:

events: {
       load: function () {

           // set up the updating of the chart each second
           setInterval(function () {
               var series = this.series[0]; //You can do this for each series if there are more
                   $.ajax({ url: "/data/url", success: function (newData) { 
                        series.setData(newData,true);//Assuming it is properly formatted.

                    });         
                }, 60000);
            }
        }