Highcharts not picking up on date - dates being interpreted as strings

Question

I'm trying to create a highchart with irregular x axis, a bit like this: http://jsfiddle.net/gh/get/jquery/1.9.1/highslide-software/highcharts.com/tree/master/samples/highcharts/demo/spline-irregular-time/

I'm pulling my data from mySQL via PHP and processing in JSON format.

I can't get highcharts/JS to read the dates in date format - it seems to be pulling them as strings.

Here's my script:

$(document).ready(function() {
    var options = {
        chart: {
            type: 'spline',
            marginRight: 130,
            marginBottom: 25
        },
        title: {
            text: 'some title',
            x: -20 //center
        },
        subtitle: {
            text: '',
            x: -20
        },
        xAxis: {
            type: 'datetime',
            dateTimeLabelFormats: { // don't display the dummy year
                month: '%e. %b',
                year: '%b'
            },
            categories: []
        },
        yAxis: {
            title: {
                text: 'L/min'
            },
            plotLines: [{
                value: 0,
                width: 1,
                color: '#808080'
            }]
        },
        plotOptions: {
            spline: {
                marker: {
                    enabled: true
                }
            }
        },

        tooltip: {
            formatter: function() {
                return '<b>'+ this.series.name +': '+
                this.y +'</b><br>'+ this.x;
            }
        },
        legend: {
            layout: 'vertical',
            align: 'right',
            verticalAlign: 'top',
            x: -10,
            y: 100,
            borderWidth: 0
        },
        series: []
    }

        $.getJSON("allreports/report.php", function(json) {

        options.xAxis.categories = json[0]['data'];
        options.series[0] = json[1];
        options.chart.renderTo = 'container';
        chart = new Highcharts.Chart(options);
    });

and here's my PHP page:

<?php
    $con = mysql_connect("localhost","db-user","db-pw");

    if (!$con) {
        die('Could not connect: ' . mysql_error());
    }

    mysql_select_db("db", $con);

    $sth = mysql_query("SELECT v1,v2 FROM table");
    $rows = array();
    $rows['name'] = 'v1';
    while($r = mysql_fetch_array($sth)) {
        $rows['data'][] = $r['v1'];
    }

    $result = array();
    array_push($result,$rows1);
    array_push($result,$rows);


    print json_encode($result, JSON_NUMERIC_CHECK);


    mysql_close($con);
?>

This outputs the following:

[{"name":"medicinetime","data":["01,01,1970,01,00,00","01,01,1970,01,33,36","01,01,1970,01,33,36","01,01,1970,01,33,36"]},{"name":"units","data":[1,3,2,2]}]

But my chart is as follows:

Any thoughts on what I'm doing wrong please? What do I need to do to get PHP/JS to pick up on this array as a series of datetimes rather than strings?

Thank you!

Answer 1

You have two issues. First, you need to convert your date-component values into an actual Javascript date object. The format you're looking for for your series data is an array of X/Y pairs. It'll look like this, using your data:

[
  [Date.UTC(1970,1,1,1,0,0),1],
  [Date.UTC(1970,1,1,1,33,36),3],
  [Date.UTC(1970,1,1,1,33,36),2],
  [Date.UTC(1970,1,1,1,33,26),2]
]

Try something like this:

var xVals= json[0].data.map( item => {
  var dateArr = point[0].join(',');
  return Date.UTC(dateArr[2],dateArr[1],dateArr[0],dateArr[3],dateArr[4],dateArr[5]);
});

var yVals = json[1].data;

options.series[0] = { 
  name: json[1].name,
  data: xVals.map( (x,i) => [x,yVals[i]])
}

Some documentation on valid formats for the series data is here: http://www.highcharts.com/docs/chart-concepts/series

Second, you have some duplicate X values there, and that's going to be confusing...you're going to want your timestamps to be unique, or it's going to be hard to make a line chart out of them.