Sorting and filtering on Date

Question

I have a date column and need to be able to both sort and filter on it. The data comes in as strings like 2010-12-23 and can pre-processed as needed. It should be shown as 23.12.2010. Some internationalization will come later.

I wonder what's the proper internal representation:

• a string like "23.12.2010" is bad for sorting (it could be done by sorting on function result, but it'd be slow)
• a string like "2010-12-23" sorts correctly, can be formatted easily, but filtering for 23.12 does not work (it could be done, but it'd be slow)
• Date would probably get sorted correctly, but filtering would be slow
• moment could be the solution, no idea

My current idea is to create an object containing both milliseconds and the displayed string, so that all operations can be fast. But I'd bet that someone was that smart before me....

---

Let's assume that showing dates in the form like 2010-12-23 is unacceptable, otherwise the problem is solved. To summarize, the problem is that I need to

• display and filter in the DD.MM.YYYY format
• sort according to the numerical value (or equivalently, as if it was in th ISO format).

Answer 1

I think the method you're proposing wouldn't run in to too many performance issues, unless you're going for really old browsers or mobile devices.

I've mocked up an example to do a quick (performance) test. First, I'm defining an object that holds a value optimized for sorting, and a value optimized for display:

var MyDate = function(dateString) {
    var date = new Date(dateString);
    var displayValue = "{0}.{1}.{2}"
        .replace("{0}", prefixZeroIfNeeded(date.getUTCDate()))
        .replace("{1}", prefixZeroIfNeeded(date.getUTCMonth() + 1))
        .replace("{2}", date.getUTCFullYear());

    return {
        sortKey: date.getTime(),
        displayValue: displayValue
    };
};

The prefixZeroIfNeeded method ensures we get the DD.MM format rather than the dd.mm one:

var prefixZeroIfNeeded = function(nr) {
    return nr < 10 ? "0" + nr : "" + nr;
};

Then, we need some data to convert:

var data = [];
var myDates = data
    .map(MyDate)
    .sort(function(date1, date2) {
        return date1.sortKey - date2.sortKey;
    });

Finally, a quick example of a very basic search function:

var searchMyDates = function(str) {
    return myDates.filter(function(myDate) {
        return myDate.displayValue.indexOf(str) !== -1;
    });
};

Now, we can create some mockup data and check how long it would actually take to A) map and sort the raw strings to the MyDate objects, and B) search for a string in our collection.

Here's how I generated the raw data:

for (var i = 0; i < 10000; i += 1) {
    var y = Math.floor(Math.random() * 101) + 1900;
    var m = prefixZeroIfNeeded(Math.floor(Math.random() * 13));
    var d = prefixZeroIfNeeded(Math.floor(Math.random() * 29));

    data.push(y + "-" + d + "-" + m);
}

Using console.time to measure, processing the data on my machine (A) takes around 40ms. Searching for the string .12. takes around 5-10ms.

Concluding: I think you were definitely on the right track and could continue work in the proposed direction. However, in my personal experience, I've learned that whenever I start work on a feature that involves dates and times, moment.js is the way to go. You'll eventually run in to daylight saving time, time zones, you name it and regret you thought it was simple...

Let me know if this is of any help.

Edit: the code in a snippet (check your browser console for output)

var data = [];

var prefixZeroIfNeeded = function(nr) {
  return nr < 10 ? "0" + nr : "" + nr;
};

// Generate random data:
for (var i = 0; i < 10000; i += 1) {
  var y = Math.floor(Math.random() * 101) + 1900;
  var m = prefixZeroIfNeeded(Math.floor(Math.random() * 13));
  var d = prefixZeroIfNeeded(Math.floor(Math.random() * 29));

  data.push(y + "-" + d + "-" + m);
}



var MyDate = function(dateString) {
  var date = new Date(dateString);
  var displayValue = "{0}.{1}.{2}"
    .replace("{0}", prefixZeroIfNeeded(date.getUTCDate()))
    .replace("{1}", prefixZeroIfNeeded(date.getUTCMonth() + 1))
    .replace("{2}", date.getUTCFullYear());

  return {
    sortKey: date.getTime(),
    displayValue: displayValue
  };
};

console.time("Map and sorting");

var myDates = data
  .map(MyDate)
  .sort(function(date1, date2) {
    return date1.sortKey - date2.sortKey;
  });

var searchMyDates = function(str) {
  return myDates.filter(function(myDate) {
    return myDate.displayValue.indexOf(str) !== -1;
  });
};

console.timeEnd("Map and sorting");

console.time("Search");

console.log("Searching for the month 12, %d results.", searchMyDates(".12.").length);


console.timeEnd("Search");