Highcharts not plotting datetime series

Question

First time experimenting with Highcharts/Php/MySQL together. I have a php script to recover a datetime series from MySQL. I am attempting to shoehorn my data into Highcharts code found here. So far I have only managed to get the chart axis & title to display in the browser, which leads me to think that there is a problem in formatting the data series. I understand that JS requires date formatting in Unix, so I have tried to replace the while loop in php code below with,

while($row = mysql_fetch_array($result)) {
$uts=strtotime($row['DATETIMES']);
$date=date("l, F j, y H:i:s",$uts);
echo $date . "\t" . $row['CCGT']. "\n";
}

Unfortunately the series still does not display. Would anyone have any insight as to why the datetime series might not be plotted?

Original php code

<?php

$con = mysql_connect("localhost","user",");

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

mysql_select_db("test", $con);

$result = mysql_query("SELECT * FROM PR") or die ("Error");
echo mysql_error();
while($row = mysql_fetch_array($result)) {
  echo $row['DATETIMES'] . "\t" . $row['CCGT']. "\n";
}

mysql_close($con);
?>

Data is returned in the format

2013-12-23 09:45:00 8389 2013-12-23 09:50:00 8478 2013-12-23 09:55:00 8761 2013-12-23 10:00:00 8980 2013-12-23 10:05:00 9050 2013-12-23 10:10:00 9010

The Highcharts code is as follows

<!DOCTYPE html>
<html>
<head>
<title></title>
<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.7.1/jquery.min.js" type="text/javascript"></script>
<script src="http://code.highcharts.com/highcharts.js"></script>
<script src="http://code.highcharts.com/modules/exporting.js"></script>

</head>
<body>

<h1>CCGT</h1>

<script type="text/javascript">
    var chart;
            $(document).ready(function() {
                var options = {
                    chart: {
                        renderTo: 'container',
                        defaultSeriesType: 'line',
                        marginRight: 130,
                        marginBottom: 25
                    },
                    title: {
                        text: 'CCGT',
                        x: -20 //center
                    },
                    subtitle: {
                        text: '',
                        x: -20
                    },
                    xAxis: {
                        type: 'datetime',
                        tickInterval: 60 * 1000, // one hour
                        tickWidth: 0,
                        gridLineWidth: 1,
                        labels: {
                            align: 'center',
                            x: -3,
                            y: 20,
                            formatter: function() {
                                return Highcharts.dateFormat('%l%p', this.value);
                            }
                        }
                    },
                    yAxis: {
                        title: {
                            text: 'Generation MW'
                        },
                        plotLines: [{
                            value: 0,
                            width: 1,
                            color: '#808080'
                        }]
                    },
                    tooltip: {
                        formatter: function() {
                                return Highcharts.dateFormat('%l%p', this.x-(1000*60)) +'-'+ Highcharts.dateFormat('%l%p', this.x) +': <b>'+ this.y + '</b>';
                        }
                    },
                    legend: {
                        layout: 'vertical',
                        align: 'right',
                        verticalAlign: 'top',
                        x: -10,
                        y: 100,
                        borderWidth: 0
                    },
                    series: [{
                        name: 'Count'
                    }]
                }
                // Load data asynchronously using jQuery. On success, add the data
                // to the options and initiate the chart.
                // This data is obtained by exporting a GA custom report to TSV.
                // http://api.jquery.com/jQuery.get/
                jQuery.get('data.php', null, function(tsv) {
                    var lines = [];
                    traffic = [];
                    try {
                        // split the data return into lines and parse them
                        tsv = tsv.split(/\n/g);
                        jQuery.each(tsv, function(i, line) {
                            line = line.split(/\t/);
                            date = Date.parse(line[0] +' UTC');
                            traffic.push([
                                date,
                                parseInt(line[1].replace(',', ''), 10)
                            ]);
                        });
                    } catch (e) {  }
                    options.series[0].data = traffic;
                    chart = new Highcharts.Chart(options);
                });
            });
</script>



</head>
<body>

<div id="container" style="width: 100%; height: 400px; margin: 0 auto"></div>

</body>
</html>

EDIT:

I have also tried UNIX_TIMESTAMP to return a unix time during the SQL query, then multiplying by 1000 to return JS time. Data.php now returns dates in the following format: 1387908000000 however initialising the charts still returns no series. How should the date be formatted from JS within the jQuery code above in order to be compatible with Highcharts?

r.vengadesh r.vengadesh · Accepted Answer · 2013-12-30T11:14:13

Try this

1.First set the date in a variable 
2.Find difference between your date and 1-1-1970
3.Multiply that value into 24*60*60*1000
4.Put this value into highchart

Highcharts not plotting datetime series

2 Answers