Search code examples
d3.jsdc.jscrossfilter

d3.time/crossfilter days are off by one

I've been trying to create a dc.js rowchart showing stats per day, my dimension and group are

  var dayNameFormat = d3.time.format("%A");
  var weekDayFormat = d3.time.format('%w'); //weekday as a decimal number [0(Sunday),6].

  var dayOfWeek = ndx.dimension(function(d) {
    return weekDayFormat(d.date) + '.' + dayNameFormat(d.date);
  });

  var dayOfWeekGroup = dayOfWeek.group().reduce(
    function(p, d) {
      ++p.count;
      p.totalPoints += +d.points_per_date;
      p.averagePoints = (p.totalPoints / p.count);
      if (d.student_name in p.studentNames) {
        p.studentNames[d.student_name] += 1
      } else {
        p.studentNames[d.student_name] = 1;
        p.studentCount++;
      }
      return p;
    },
    function(p, d) {
      --p.count;
      p.totalPoints -= +d.points_per_date;
      p.averagePoints = (p.totalPoints / p.count);
      if (p.studentNames[d.student_name] === 0) {
        delete p.studentNames[d.student_name];
        p.studentCount--;
      }
      return p;
    },
    function() {
      return {
        count: 0,
        totalPoints: 0,
        averagePoints: 0,
        studentNames: {},
        studentCount: 0
      };
    });

and chart

  dayOfWeekChart
    .width(250)
    .height(180)
    .margins({
      top: 20,
      left: 20,
      right: 10,
      bottom: 20
    })
    .dimension(dayOfWeek)
    .group(dayOfWeekGroup)
    .valueAccessor(function(d) {
      return d.value.totalPoints
    })
    .renderLabel(true)
    .label(function(d) {
      return d.key.split('.')[1] + '(' + d.value.totalPoints + ' points)';
    })
    .renderTitle(true)
    .title(function(d) {
      return d.key.split('.')[1];
    })
    .elasticX(true);

I expected the results to match those of my database query

enter image description here

The total values are correct, but the days have been offset by a day (Sunday has Monday's total)

enter image description here

My fiddle https://jsfiddle.net/santoshsewlal/txrLw9Lc/ I've been doing my head in trying to get this right, any help will be great. Thanks

Solution

  • It appears to be a UTC date/time problem. Dealing with data from multiple time zones is always confusing!

    All of your timestamps are very near to the next day - they are all timestamped at 22:00. So it depends on the timezone which day they should be interpreted as. I guess you might be in the eastern hemisphere, which adds a couple of hours to these timestamps when you read them in your spreadsheet?

    You're chopping off the time with substr:

    d.date = dateFormat.parse(d.activity_date.substr(0, 10));

    I'd suggest trying to parse the whole time instead:

      var dateFormat = d3.time.format('%Y-%m-%dT%H:%M:%S.%LZ');
  data.forEach(function(d, i) {
    d.index = i;
    d.date = dateFormat.parse(d.activity_date);

    However, I'm no expert in timezones so I can't promise anything. Just pointing out where the problem likely lies.