Lavachart Datatable->toJson provides f column and v column for date on x axis with different timezone

Question

Version :"khill/lavacharts": "3.1.*",

Controller provides carbon date object and datatable is completed as per below ;

In PHP code

  $dataTable = \Lava::DataTable();
    $formatter  = \Lava::DateFormat([
        'pattern' => 'MMM d, HH:mm',
        //'timeZone' => '', //same results without timezone parameter or with 'timeZone' => 2,
    ]);

  $DataTable->addDateColumn('closing hour', $formatter)
            ->addNumberColumn('closing price')
            ->addNumberColumn('product evaluation')
            ->setDateTimeFormat('Y-m-d H:i:s')
            ->setTimezone('UTC') ; //added but no effect

for (...){
           //$hour_start-->toIso8601String() ==  2018-09-07T17:00:00+00:00 6430.4420188271
            $dataTable->addRow([$hour_start->toDateTimeString(), $hourly_closing_price ,$hourly_value]);
}

 **return $dataTable->toJson();//added after answer of WhiteHat**
 Log::info(' dataTable '.$dataTable->toJson());

However while logged php json of datatable is as per below, for each row :

{"c":[{"v":"Date(2018,8,7,17,0,0)"},{"v":6430.442018827109}...

In javascript after ajax call to my Laravel controller, received json is a per below

{"c":[{"v":"2018-09-07T15:00:00.000Z","f":"Sep 7, 17:00"},{"v":6430.442018827109},{"v":0}]} .

There a 2 hour difference between f column (my UTC date) and v column (my UTC date minus 2h).

I changed my browser local time zone for test but no effect. Data provided are in UTC, server is in UTC and timezone set on chart is UTC but somewhere in google chart, a 2h difference is applied.

It seems google server doesnt consider the dates sent as UTC time and change the v value while keeping the f formatted value

With

  $formatter  = \Lava::DateFormat([
            'pattern' => 'MMM d, HH:mm',
            'timeZone' => 0,
        ]);

=> f and v column on same timezone but changed as if input date was not UTC (but UTC+2)

{"c":[{"v":"2018-09-07T15:00:00.000Z","f":"Sep 7, 15:00"},{"v":6430.442018827109},{"v":0}]}

Any clue someone?

Answer 1

i'm not familiar with Lavacharts,
but I would question the format of the data when it actually reaches the browser.
whether, it is actually in json format.

not sure what addRow does here, but it appears to be loading a simple array,
not a json object.

addRow([$hour_start->toDateTimeString(), $hourly_closing_price ,$hourly_value]);

in javascript, when you create a date from a string,
in most cases, it will be adjusted for the timezone.
see this answer for more info there. --> Why does Date.parse give incorrect results?

however, google's json date string representation will result in the exact date,
with no adjustment for timezone.

take a look at the following example,
in the first row, google's json date string is used.
in the second, we use the new Date() constructor from a formatted string.

you'll notice the browser does not change the timezone of the first row,
but does on the second.

google.charts.load('current', {
  packages: ['table']
}).then(function () {

  var data = new google.visualization.DataTable({
    cols: [
      {type: 'datetime'}
    ],
    rows: [
      {c:[{v: 'Date(2018,8,7,17,0,0)'}]},
      {c:[{v: new Date('2018-09-07T17:00:00+00:00')}]}
    ]
  });
  
  var formatDate = new google.visualization.DateFormat({
    pattern: 'MM/dd/yyyy HH:mm:ss'
  });
  formatDate.format(data, 0);
  
  var table = new google.visualization.Table(document.getElementById('table'));
  table.draw(data);
  
  document.getElementById('test-date0').innerHTML = data.getValue(0, 0);
  document.getElementById('test-date1').innerHTML = data.getValue(1, 0);

});

div {
  margin-bottom: 6px;
}

<script src="https://www.gstatic.com/charts/loader.js"></script>
<div>Google table chart</div>
<div id="table"></div>
<div>raw data</div>
<div id="test-date0"></div>
<div id="test-date1"></div>

---

EDIT

one option might be to use a data view to adjust the date by the timezone offset on the browser,
this should put the date back to the original value sent from the server...

see following working snippet...

google.charts.load('current', {
  packages: ['table']
}).then(function () {

  var data = new google.visualization.DataTable({
    cols: [
      {type: 'datetime'}
    ],
    rows: [
      {c:[{v: 'Date(2018,8,7,17,0,0)'}]},
      {c:[{v: new Date('2018-09-07T17:00:00+00:00')}]}
    ]
  });
  
  var formatDate = new google.visualization.DateFormat({
    pattern: 'MM/dd/yyyy HH:mm:ss'
  });

  // create data view
  var view = new google.visualization.DataView(data);
  view.setColumns([{
    calc: function (dt, row) {
      // one minute in milliseconds
      var oneMinute = (60 * 1000);

      // get date from data table
      var rowDate = dt.getValue(row, 0);

      // Adjust date for timezone
      rowDate = new Date(rowDate.getTime() + (oneMinute * rowDate.getTimezoneOffset()));

      // return new value and formatted value
      return {
        v: rowDate,
        f: formatDate.formatValue(rowDate)
      };
    },
    type: data.getColumnType(0)
  }]);

  var table = new google.visualization.Table(document.getElementById('table'));
  table.draw(view);
  
  document.getElementById('test-date0').innerHTML = view.getValue(0, 0);
  document.getElementById('test-date1').innerHTML = view.getValue(1, 0);

});

div {
  margin-bottom: 6px;
}

<script src="https://www.gstatic.com/charts/loader.js"></script>
<div>Google table chart</div>
<div id="table"></div>
<div>raw data</div>
<div id="test-date0"></div>
<div id="test-date1"></div>

Answer 2

As explained by WhiteHat, this is an issue on how my browser & googleChart interpret as local dates the json containing google dataTable with utc dates.

1) What is done by my browser with a date as : new Date(2018,8,7,6,0,0) :

console.log('time2 '+ new Date(2018,8,7,6,0,0)) ->Fri Sep 07 2018 06:00:00 GMT+0200 (Romance Daylight Time)

What i want :

console.log('time1'+ new Date(Date.UTC(2018,8,7,6,0,0))) ->Fri Sep 07 2018 08:00:00 GMT+0200 (Romance Daylight Time)

see point 2 for solution.

2) I need to retrieve my json utc date on indeed this format : new Date(2018,8,7,6,0,0)

Javascript call to google chart changes received server utc dates (ex: {"v":"Date(2018,8,7,17,0,0)"}) in json in a local date formatted as [{"v":"2018-09-07T15:00:00.000Z","f":"Sep 7, 17:00"} so i need to intercept the string date before it gets changed

 var dataTable
$.getJSON('/chart', function (dataJson) {
   // dataTable dataJson on format : "v":"Date(2018,7,31,19,0,0)"
   dataTable = dataJson
   dataToLoad = $.extend(true,{},dataJson); //clone it as the object dataToLoad  will be modified by google chart when loaded per lava

   lava.loadData('chart', dataToLoad , function (data) {
         console.log('data'+JSON.stringify(dataToLoad )) //dataTable dates are put on format : "v":"2018-08-31T16:00:00.000Z","f":"Aug 31, 18:00"           
    });
})

use dataTable...

//To get the right timezone on my dates, i did something like:

  string_timestamp ; string_timestamp = datatable.data['rows'][item.row]['c'][0]['v'] //this is my received UTC date formatted as "Date(2018,8,7,17,0,0)" in dataTable 
  temp=(string_timestamp.replace("Date(","")).replace(")","")                          
  str1= temp.split(',');
  date2=new Date(Date.UTC(str1[0],str1[1],str1[2],str1[3],str1[4],str1[5]))