CODEWITHSUNDEEP
javascriptphpmysqlgoogle-visualization
Sam
Sam

Reputation: 185

MySQL Datetime in Google Chart

I'm working on a chart from MySQL, it worked fine as a linechart but when I changed to annotationchart it gave me the following error due to it needing a date/time, I changed the type to datetime (was string) and still have the error.

Type mismatch. Value 2014-07-23 19:03:16 does not match type datetime

Original Code

 <?php
        $con=mysql_connect("ip","user","pass") or die("Failed to connect with database!!!!");
        mysql_select_db("db", $con); 

        $sth = mysql_query("SELECT * FROM db.table");

        $data = array (
      'cols' => array( 
        array('id' => 'date', 'label' => 'date', 'type' => 'datetime'), 
        array('id' => 'Temp', 'label' => 'Temp', 'type' => 'number'), 
        array('id' => 'Humid', 'label' => 'Humid', 'type' => 'number')
    ),
    'rows' => array()
);

while ($res = mysql_fetch_assoc($sth))
    // array nesting is complex owing to to google charts api
    array_push($data['rows'], array('c' => array(
        array('v' => $res['TIME']), 
        array('v' => $res['TEMP']), 
        array('v' => $res['HUMID'])
    )));

?>

<html>
  <head>
    <script type="text/javascript" src="https://www.google.com/jsapi"></script>
    <script type="text/javascript">
      google.load('visualization', '1.1', {'packages':['annotationchart']});
      google.setOnLoadCallback(drawChart);
      function drawChart() {
            var bar_chart_data = new google.visualization.DataTable(<?php echo json_encode($data); ?>);
        var options = {
          title: 'Weather Station'
        };
        var chart = new google.visualization.AnnotationChart(document.getElementById('chart_div'));
        chart.draw(bar_chart_data, options);
      }
    </script>
</head>
            <body>
                <div id="chart_div" style="width: 900px; height: 500px;"></div>
            </body>
        </html>

Upvotes: 1

Views: 6371

Answers (2)

asgallant
asgallant

Reputation: 26340

The "datetime" data type requires a very specific syntax for data input. When using JSON, the data should be constructed as a string in this format: 'Date(year, month, day, hours, minutes, seconds, milliseconds)', where all options after month are optional (default is 1 for day and 0 for all others) and month is zero-indexed (so January is 0 not 1).

You can convert your date times like this:

while ($res = mysql_fetch_assoc($sth)) {
    // assumes dates are patterned 'yyyy-MM-dd hh:mm:ss'
    preg_match('/(\d{4})-(\d{2})-(\d{2})\s(\d{2}):(\d{2}):(\d{2})/', $res['TIME'], $match);
    $year = (int) $match[1];
    $month = (int) $match[2] - 1; // convert to zero-index to match javascript's dates
    $day = (int) $match[3];
    $hours = (int) $match[4];
    $minutes = (int) $match[5];
    $seconds = (int) $match[6];
    array_push($data['rows'], array('c' => array(
        array('v' => "Date($year, $month, $day, $hours, $minutes, $seconds)"), 
        array('v' => $res['TEMP']), 
        array('v' => $res['HUMID'])
    )));
}

Upvotes: 3

Sam
Sam

Reputation: 185

Thanks to Asgallant and much fiddling the following code fixed all my problems

    array('v' => 'Date(' . date('Y,n,d,H,i,s', strtotime($res['TIME'])).')'), 
    array('v' => floatval($res['TEMP'])), 
    array('v' => floatval($res['HUMID']))

I found a streamlined way to convert MySQL Datetime to javascript using the PHP Date function and although the Temp and Humid values were stored as Decimals in MySQL, javascript didn't like it so I used floatval to make those work also. Now I have a happy, working Annotation Chart!

Upvotes: 0

Related Questions