Trendlines and dates on linecharts.

Steve Wright

unread,

Jan 16, 2017, 4:35:45 AM1/16/17

to Google Visualization API

Hi all

I'm having an issue with trying to add a trend line to a chart with dates on the x-axis.

I know I need to change the data type to 'number' or 'datetime' but can't seem to make anything other than 'string' plot anything.

The data is extracted from a MySQL data base that has starttime stored as a datetime.

Some research has suggested I need to convert the datetime string to a json date but I have no idea how to even get started with this process.

Code below.

Can anyone assist please?

<?php 

    try {
      /* Establish the database connection */
      $conn = new PDO("mysql:host=localhost;dbname=$dbname", $username, $password);
      $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
      
//DOWNLOAD CHART
      /* select all the weekly tasks from the table googlechart */
      $dresult = $conn->query('SELECT starttime as newStart, download FROM speeddata');

      $drows = array();
      $dtable = array();
      $dtable['cols'] = array(

        array('label' => 'StartTime', 'type' => 'string'),                      /*This is the thing that needs changing*/
        array('label' => 'Download Speed', 'type' => 'number'),
        //array('label' => 'Ping', 'type' => 'number')
    );
        /* Extract the information from $result */
        foreach($dresult as $d) {

          $dtemp = array();

          // the following line will be used to slice the chart

          $dtemp[] = array('v' => (string) $d['newStart']);                 /* and probably this */

          // Values of each slice

          $dtemp[] = array('v' => (DOUBLE) $d['download']); 
         // $temp[] = array('v' => (DOUBLE) $r['server_name']); 
          
          $drows[] = array('c' => $dtemp);
        }

    $dtable['rows'] = $drows;
    $ptable['rows'] = $prows;        
    
    // convert data into JSON format
    $djsonTable = json_encode($dtable);
    echo $djsonTable;
    } catch(PDOException $e) {
        echo 'ERROR: ' . $e->getMessage();
    }
    
    ?>

    <html lang="en">
        <head>
        <meta charset="UTF-8">

        <!--Reload page on resize-->
        <script type="text/javascript">
        var currheight = document.documentElement.clientHeight;
        window.onresize = function(){
            if(currheight != document.documentElement.clientHeight) {
            location.replace(location.href);
            }    
        }
        </script>
        <!--Load the Ajax API-->
        <script type="text/javascript" src="https://www.google.com/jsapi"></script>
        <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.2/jquery.min.js"></script>
        <script type="text/javascript">

        // Load the Visualization API and the piechart package.
        google.load('visualization', '1', {'packages':['corechart']});

        // Set a callback to run when the Google Visualization API is loaded.
        google.setOnLoadCallback(downloadChart);

        
        function downloadChart() {

          // Create our data table out of JSON data loaded from server.
          var ddata = new google.visualization.DataTable(<?=$djsonTable?>);
          var options = {

              is3D: 'true',
              width: '100%',
              height: 500,
              hAxis:{title: 'Time', 
                    direction:1, 
                    slantedText:true, 
                    slantedTextAngle:90,
                    textStyle : { fontSize: 8} // or the number you want
                    },
              vAxis:{title: 'Speed Mbit/s'},
              legend: { position: 'bottom' },
              chartArea: { top: 45, height: '40%',
                            backgroundColor: {
                            stroke: '#ccc',
                            strokeWidth: 1},
                   
                }
                //trendlines: { 0: {color: 'green',} }    // Draw a trendline for data series 0.        
                
                
            };
          // Instantiate and draw our chart, passing in some options.
          // Do not forget to check your div ID

          var chart = new google.visualization.LineChart(document.getElementById('download_chart_div'));
          chart.draw(ddata, options);
       
        }

        </script>

    </head>

     <body class="site">

    <main class="site-content">
        <!--this is the div that will hold the chart-->

        <div id="chart_title">Download Speed</div>
        <div id="download_chart_div"></div>
        <hr><br>


        </main>

      </body>
    </html>

Steve Wright

unread,

Jan 16, 2017, 4:45:09 AM1/16/17

to Google Visualization API

I should also add that the date is in

2017-01-12 13:40:07

format and I can get epoch date by selecting UNIX_TIMESTAMP(starttime) but nothing seems to help!

Steve

darren....@internode.on.net

unread,

Jan 27, 2017, 2:31:22 PM1/27/17

to Google Visualization API

Hi Steve,

I am having the same issue with date time and was wondering if you managed to resolve it?

As with your experience, the only time I get any result is when I use the datetime in a string format.

I decided to echo the json to try and determine what was going on. Out of the whole datetime string, only the year was being sent.

Steve Wright

unread,

Jan 28, 2017, 6:53:54 PM1/28/17

to Google Visualization API

Hi Darren
Yeah I managed to solve it! I needed to convert the date to something json understood:-

I ended up using a function which I borrowed from here:-

https://www.agcross.com/2014/10/using-php-to-convert-mysql-datetimes-to-javascript-format/

to convert the string to the correct format

    function JSdate($in,$type){
        if($type=='date'){
            //Dates are patterned 'yyyy-MM-dd'
            preg_match('/(\d{4})-(\d{2})-(\d{2})/', $in, $match);
        } elseif($type=='datetime'){
            //Datetimes are patterned 'yyyy-MM-dd hh:mm:ss'
            preg_match('/(\d{4})-(\d{2})-(\d{2})\s(\d{2}):(\d{2}):(\d{2})/', $in, $match);
        }

        $year = (int) $match[1];
        $month = (int) $match[2] - 1; // Month conversion between indexes
        $day = (int) $match[3];

        if ($type=='date'){
            return "Date($year, $month, $day)";
        } elseif ($type=='datetime'){
            $hours = (int) $match[4];
            $minutes = (int) $match[5];
            $seconds = (int) $match[6];
            return "Date($year, $month, $day, $hours, $minutes, $seconds)";
        }
    }

Hope that helps

Reply all

Reply to author

Forward