Timezone Offset Error

353 views
Skip to first unread message

darren....@internode.on.net

unread,
Jan 24, 2017, 2:57:25 AM1/24/17
to Google Visualization API
Hi All,
I am new to Google charts and this forum.
For some time now I have been trying to graph temperature against time from data I am logging to MySQL, using a variety of different applications / java scripts / methods.
It was not until I first tried Google Charts that I was able to achieve any kind of success.

My DB table has 3 columns:-

  • ID
  • Time
  • Temperature
Starting with a working Pie Chart example, I was able to make the necessary changes to create a fully functioning line chart to plot temperature against ID, however when I update the code to plot against time I get the following error:-

Object doesn't support property or method 'getTimezoneOffset'

Any suggestions as to where in the code below could be causing the problem, would be greatly appreciated.

Thanks
Darren


 $result = $mysqli->query('SELECT Temperature, Time FROM dp00000001 ORDER BY ID DESC LIMIT 2000');
  
  $rows = array();
  $table = array();
  $table['cols'] = array(
 
    array('label' => 'Time', 'type' => 'datetime'),
    array('label' => 'Temperature', 'type' => 'number')
);
    /* Extract the information from $result */
    foreach($result as $r) {
      $temp = array();
    
      $temp[] = array('v' => (int) $r['Time']);

      $temp[] = array('v' => (float) $r['Temperature']);
      $rows[] = array('c' => $temp);
    }
$table['rows'] = $rows;

$jsonTable = json_encode($table);
?>

<html>
  <head>
<meta http-equiv="refresh" content="600">
    <!--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(drawChart);
    function drawChart() {
      // Create our data table out of JSON data loaded from server.
      var data = new google.visualization.DataTable(<?=$jsonTable?>);
      var options = {
           title: 'Temperature',
          curveType: 'function',
          width: 1200,
          height:400,
      explorer:{maxZoomIn:10,   actions:["dragToZoom","rightClickToReset"],axis: "horizontal"
    }
        };
      // 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('chart_div'));
      chart.draw(data, options);
    }
    </script>
  </head>
  <body>
    <!--this is the div that will hold the pie chart-->
    <div id="chart_div"></div>
  </body>
</html>

Daniel LaLiberte

unread,
Jan 24, 2017, 4:53:25 PM1/24/17
to Google Visualization API
Can we see what is in your data?   That makes all the difference in determining what happened when it appears to be a data-related problem.

It appears your data table will have a datetime column as the domain values, but your data is integers.  It *might* work if you are specifying milliseconds, but that is not a supported mechanism.  

If your time values are really only times during the day regardless of day, then you probably want to use the timeofday type instead.  See the documentation page on dates and times here: https://developers.google.com/chart/interactive/docs/datesandtimes

I would also recommend first replacing your LineChart with a Table chart, just to see what data you are getting. 


--
You received this message because you are subscribed to the Google Groups "Google Visualization API" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-visualization-api+unsub...@googlegroups.com.
To post to this group, send email to google-visualization-api@googlegroups.com.
Visit this group at https://groups.google.com/group/google-visualization-api.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-visualization-api/e105c322-3f54-49f5-951d-63f44d7d673b%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.



--

darren....@internode.on.net

unread,
Jan 24, 2017, 9:12:55 PM1/24/17
to Google Visualization API
Hi Daniel,
  • ID - Int(11)
  • Time - datetime
  • Temperature - double
ID     -      Datetime          - Temperature
6065 - 2017-01-24 21:00: - 29.375
6064 - 2017-01-24 20:42: - 29.125
6063 - 2017-01-24 20:29: - 28.875

To create the table chart, do I just change the chart type from LineChart to TableChart here:-

var chart = new google.visualization.LineChart(document.getElementById('chart_div'));



Daniel LaLiberte

unread,
Jan 24, 2017, 9:26:56 PM1/24/17
to Google Visualization API
I can't really tell from the data that you copy as strings into your message because they could have just been formatted as you expect to see them.  But I would guess your datetime values really are dates and times, in which case, you should probably use the "Date(...)" string notation rather than just converting the datetime values into milliseconds.  This may resolve the timezone offset problem you are experiencing.  

To display in a Table chart, load package 'table' and call google.visualization.Table(..)


--
You received this message because you are subscribed to the Google Groups "Google Visualization API" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-visualization-api+unsub...@googlegroups.com.
To post to this group, send email to google-visualization-api@googlegroups.com.
Visit this group at https://groups.google.com/group/google-visualization-api.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-visualization-api/d2171d3c-67cd-4cd7-8c7f-10e3e9004cbb%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

darren....@internode.on.net

unread,
Jan 26, 2017, 5:54:00 PM1/26/17
to Google Visualization API
I still had no luck in getting the table chart to work, however I did manage to echo the json and it looks like this where the problem is. for some reason the json output is only showing the year, not the whole date time.

I have also managed to echo the datetime correctly (prior to json conversion )from the last sample, so I know that the date time format is ok.




On Tuesday, 24 January 2017 13:57:25 UTC+11, darren....@internode.on.net wrote:

darren....@internode.on.net

unread,
Jan 27, 2017, 7:21:31 PM1/27/17
to Google Visualization API
I think that this is where I need to make the changes to the above code, but just not sure how to change
         $temp[] = array('v' => (float) $r['Time']);
Any suggestions?

darren....@internode.on.net

unread,
Feb 18, 2017, 11:20:04 PM2/18/17
to Google Visualization API
I am getting closer, however struggling to get over the last hurdle.
My code has now been modified as per below, breaking the date and time down before encoding with json.
The resulting web page can be seen in the attached image, I have echoed the json to the screen for diagnostics. There are 10 entries starting at 18th Feb 22:59 and ending 18th Feb 19:16 however on the graph it is showing Jul 20, 1903 - Jul 24, 1903.
if I change the format of this line to have a ":" in place of "," I get the original error Object doesn't support property or method 'getTimezoneOffset'
$temp[] = array('v' => 'Date('.date('m',strtotime($r['Time'])).','.(date('d',strtotime($r['Time']))).','.(date('H',strtotime($r['Time']))).':'.date('i',strtotime($r['Time'])).')');

Any help would be greatly appreciated
Thanks
Darren



 
 $mysqli = new mysqli($DB_HOST, $DB_USER, $DB_PASS, $DB_NAME);
  if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
  }
  $result = $mysqli->query('SELECT Temperature, Time FROM dp00000001 ORDER BY ID DESC LIMIT 10');

  $rows = array();
  $table = array();
  $table['cols'] = array(
    array('label' => 'Time', 'type' =>'datetime'),
    array('label' => 'Temperature', 'type' => 'number')
);
    foreach($result as $r) {
      $temp = array();
      $temp[] = array('v' => 'Date('.date('m',strtotime($r['Time'])).','.(date('d',strtotime($r['Time']))).','.(date('H',strtotime($r['Time']))).','.date('i',strtotime($r['Time'])).')');

     $temp[] = array('v' => (float) $r['Temperature']);
      $rows[] = array('c' => $temp);
    }
$table['rows'] = $rows;
$jsonTable = json_encode($table);
echo $jsonTable;

?>
<html>
  <head>
<meta http-equiv="refresh" content="600">
 
    <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">
    google.load('visualization', '1', {'packages':['corechart']});

    google.setOnLoadCallback(drawChart);
    function drawChart() {

      var data = new google.visualization.DataTable(<?=$jsonTable?>);
      var options = {
          haxis:{format:'mm-dd hh:ii'},
          title: 'Temperature',
          curveType: 'function',
          width: 1200,
          height:400,
      
      explorer:{maxZoomIn:10,   actions:["dragToZoom","rightClickToReset"],axis: "horizontal"
    }
        };
  
      var chart = new google.visualization.LineChart(document.getElementById('chart_div'));
      chart.draw(data, options);
    }
    </script>
  </head>
  <body>
   
web page.bmp

Daniel LaLiberte

unread,
Feb 18, 2017, 11:44:04 PM2/18/17
to Google Visualization API
It appears that you are missing the year value from your dates.  You must start with the four digit year, e.g. 2017.

--
You received this message because you are subscribed to the Google Groups "Google Visualization API" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-visualization-api+unsub...@googlegroups.com.
To post to this group, send email to google-visualization-api@googlegroups.com.
Visit this group at https://groups.google.com/group/google-visualization-api.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-visualization-api/751ef014-fd7e-40e5-a91c-7e6f8647dae9%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

darren....@internode.on.net

unread,
Feb 19, 2017, 12:49:10 AM2/19/17
to Google Visualization API
I have made a few changes and somehow managed to fluke 99% success.
My Graph now is working however it is in UTC time. When I try and add +11hr offset I get the original error.
Other than this I am happy with it.

I have the offset working on the webpage text, (not sure if this is the best way to do it, but it is working) but if I try and add an offset to the datetime used for the graph, I get the original error 
Does anyone have a suggestion on how to best modify this code to include the +11 hr offset?
 
Thanks
Darren

$mysqli = new mysqli($DB_HOST, $DB_USER, $DB_PASS, $DB_NAME);
  if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
  }
  $result = $mysqli->query('SELECT Temperature, Time FROM dp00000001 ORDER BY ID DESC LIMIT 20');
   $last = $mysqli->query('SELECT Temperature, Time FROM dp00000001 ORDER BY ID DESC LIMIT 1');

  $rows = array();
  $table = array();
  $table['cols'] = array(
    array('label' => 'Time', 'type' =>'datetime'),
    array('label' => 'Temperature', 'type' => 'number')
);
    foreach($result as $r) {
      $temp = array();
      $temp[] = array('v' => 'Date('.date('Y',strtotime($r['Time'])).','.(date('m',strtotime($r['Time']))-1).','.(date('d',strtotime($r['Time']))).',' .date('H',strtotime($r['Time'])).',' .date('i',strtotime($r['Time'])).')');

     $temp[] = array('v' => (float) $r['Temperature']);
      $rows[] = array('c' => $temp);
    }
$table['rows'] = $rows;
$jsonTable = json_encode($table);
echo $jsonTable;
$row = $last->fetch_array(MYSQLI_ASSOC);
$temp = $row['Temperature'];
$time = $row['Time'];
$timetz = date('H:i:s', strtotime($time . ' + 11 hours'));
$datetz = date('d-m-Y', strtotime($time . ' +11 hours'));
$nowtime = date('H:i:s', strtotime ($time . ' + 11 Hours'));
echo "<h1 style='text-align:center;'> Home Weather </h1>";
echo "<p style='text-align:center;'> Temperature $temp degrees C </h1>";
echo "<p style='text-align:center;'> Time $timetz </h1>";
echo "<p style='text-align:center;'> Date $datetz </h1>";
echo "<p style='text-align:center;'>  </h1>";

?>
<html>
  <head>
<meta http-equiv="refresh" content="600">
 
    <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">
    google.load('visualization', '1', {'packages':['corechart']});

    google.setOnLoadCallback(drawChart);
    function drawChart() {

      var data = new google.visualization.DataTable(<?=$jsonTable?>);
      var options = {
          haxis:{format:'mm-dd hh:ii'},
          title: 'Temperature/UTC Time',

          curveType: 'function',
          width: 1200,
          height:400,
      
      explorer:{maxZoomIn:10,   actions:["dragToZoom","rightClickToReset"],axis: "horizontal"
    }
        };
  
      var chart = new google.visualization.LineChart(document.getElementById('chart_div'));
      chart.draw(data, options);
    }
    </script>
  </head>
  <body>
   
web page.bmp

darren....@internode.on.net

unread,
Feb 19, 2017, 1:04:13 AM2/19/17
to Google Visualization API
Sorry Daniel, I posted my last reply before I saw you response. Indeed adding the year was one of the changes I made.
Any suggestions on the timezone offset? 
 
Reply all
Reply to author
Forward
0 new messages