Dates on X-Axis Don't Match Data That Drives the Chart

[wsessomsParticipant]

I am having a problem with the line chart widget. The data that is retrieved via json is not being displayed along the x-axis correctly.
I have attached a screenshot of the data and the resulting chart. You can see that in the screenshot the data’s first date is 06/11/2014 but on the chart the first date is 06/10/2014. This throws the whole chart off. The last date should be 06/22/2014 but on the chart it is 06/21/2014.

I have include my mySQL query and the code used to create the chart.
I have tried everything I can think of to correctly render the chart but have not been able to solve the problem.

Your assistance is appreciated.

$(document).ready(function () {
// The queryToObject function
var queryToObject = function(q) {
var col, i, r, _i, _len, _ref, _ref2, _results;
data = [];
for (i = 0, _ref = q.ROWCOUNT; 0 <= _ref ? i < _ref : i > _ref; 0 <= _ref ? i++ : i–) {
r = {};
_ref2 = q.COLUMNS;
for (_i = 0, _len = _ref2.length; _i < _len; _i++) {
col = _ref2[_i];
r[col.toLowerCase()] = q.DATA[col][i];
}
data.push(r);
}
return data;
};

$.ajax({
type: “POST”,
url: “http://mysite/charts.cfc”,
data: { method: ‘lineData’, userID:userID },
cache: false,
dataType: “json”,
success: function(sdata) {
var sdata = queryToObject(sdata); // Convert Coldfusion Query to Javascript Object
var data = JSON.stringify(sdata);
$(“#main”).html(data); //output data to screen to check format
dspChart(data);

}
})
})

function dspChart(data){
var tmpdata = data;
var source =
{
datatype: “json”,
datafields: [
{ name: ‘expensedate’, type: ‘date’},
{ name: ‘cost’, type: ‘number’},
{ name: ‘total’, type: ‘number’},
{ name: ‘name’, type: ‘string’ }
],
localdata: tmpdata
};

dataAdapter = new $.jqx.dataAdapter(source,
{
autoBind: true,
async: false,
loadError: function () { xhr, status, error }
});
// prepare jqxChart settings
var settings = {
title: “Expenses”,
description: “”,
showLegend: false,
enableAnimations: true,
padding: { left: 15, top: 25, right: 65, bottom: 25 },
titlePadding: { left: 0, top: 0, right: 0, bottom: 10 },
source: dataAdapter,
categoryAxis:
{
text: ‘Category Axis’,
textRotationAngle: 0,
description: ‘Dates’,
dataField: ‘expensedate’,
formatFunction: function(value)
{
return dataAdapter.formatDate(value, “MM/dd/yyyy”);
},
toolTipFormatFunction: function (value) {
return value.getDate() + ‘-‘ + months[value.getMonth()] + ‘-‘ + value.getFullYear();
},
type: ‘basic’,
baseUnit: ‘day’,
showTickMarks: true,
tickMarksInterval: 1,
tickMarksColor: ‘#888888’,
unitInterval: 1,
showGridLines: true,
gridLinesInterval: 1,
gridLinesColor: ‘#888888’,
valuesOnTicks: false,
axisSize: ‘auto’
},

colorScheme: ‘scheme05’,
seriesGroups:
[
{
showLabels: true,
type: ‘line’,
columnsGapPercent: 100,
seriesGapPercent: 5,
valueAxis:
{
unitInterval: 50,
minValue: 0,
maxValue: ‘auto’,
displayValueAxis: true,
description: ‘Cost in Dollars ($)’,
axisSize: ‘auto’,
tickMarksColor: ‘#888888’
},
series: [
{ dataField: ‘total’, displayText: ‘Total Spent’ },
]
}
]
};
}

[wsessomsParticipant]

I am using ColdFusion 10 and mySQL. The first screenshot is a table showing the values for the records by Category.
The chart show the amount spent by date. So the query totals the records by date.

Below is the query for the line chart,

<cfquery name=”chartQuery” datasource=”#datasource#”>
SELECT
expense_date.expenseDate,
expenses.cost,
expenses.name,
SUM(expenses.cost) as total
FROM expenses
INNER JOIN expense_date ON expenses.dateID = expense_date.dateID WHERE expenses.userID = ‘#userID#’ Group By expense_date.expenseDate order by expense_date.expenseDate
</cfquery>

<cfreturn #SerializeJSON(chartQuery,true)#>

[Peter StoevKeymaster]

Hi wsessoms,

I think you experience a classical Time Zone issue due to the fact that you try to load Strings and represent them as Date Objects. The problem is that your String does not contain Time-Zone information. For more information about JavaScript Date Object, see: https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Date. If you pass a String instead of JavaScript Date object, then make sure that you will have to take into account the TimeZone and Daylight offset as well i.e your String should contain that data.

Best Regards,
Peter Stoev

jQWidgets Team
http://www.jqwidgets.com/

[wsessomsParticipant]

Thanks Peter for the quick reply. I’m not sure how to apply this info to my project yet but I am going to take a look at the link you supplied. Thanks again.

[wsessomsParticipant]

After thinking about the timezone issue it occurred to me that at most there would be an time offset of 3 hours coast to coast. The issue I am experiencing accounts for a whole 24 hour offset. It turns out that the problem did surround the date as a string object being passed in through the json response. I changed the expensedate type from date to string and the problem corrected itself.
Thanks again. Hope this help someone else that might run into the problem

[Author]

Posts