sort dates column

[isbkch]

I didn't find anywhere in the doc how does DynaTable sort dates. I must be missing something here because I didn't find anywhere how to specify the column type for sorting..

[JangoSteve]

That's one of the things I'm currently working on making a little easier. Right now, you just have to read the data in as a date. If your column was called "date", it'd look like this:

  $('#example-table').dynatable({
    readers: {
      'date': function(el, record) {
        return Date.parse(el.innerHTML);
      }
    },
    writers: {
      'date': function(record) {
        return record.date.toString();
      }
    }
  });

This will make the record.date attribute in the cached JSON dataset have an actual date object as the value instead of a string representation of the date object. That's why you'd have to create the separate writer function though, otherwise, it'll just write the raw date object back to the table whenever it redraws (after initialization, pagination, filtering, sorting, etc).

Alternatively, instead of creating both a reader and a writer (one for initially interpreting the data on load, the other for every time dynatable redraws the table content), you could read the formatted date into a separate attribute and use that instead:

<table>
  <thead>
    <th data-dynatable-sorts="parsedDate">Date</th>
  </thead>
  ...
</table>

  $('#example-table').dynatable({
    readers: {
      'date': function(el, record) {
        record.parsedDate = Date.parse(el.innerHTML);
        return el.innerHTML;
      }
    }
  });

This will make the record.date attribute stay as the text representation of the date, unchanged, so you don't have to write a custom writer function, since it'll just be written back to the table as it was read. To do the sorting though, this method reads the parsed date into a separate record.parsedDate attribute, and tells dynatable to use that attribute when sorting on the "date" column.

I'll add a more in-depth section to the docs on sorting here shortly. Thanks for reminding me this documentation is needed.

[JangoSteve]

I'll also mention it here for the sake of completeness, you can also specify your own sorting functions.

$('#example-table').dynatable({
  dataset: {
    sorts: {
      'date': 'dateSort'
    }
  }).bind('dynatable:init', function(e, dynatable) {
    dynatable.sorts.functions['dateSort'] = function(a, b, attr, direction) {
      return a[attr] === b[attr] ? 0 : (direction > 0 ? a[attr] - b[attr] : b[attr] - a[attr]);
    }
  });

Though, FYI, dynatable already has a number sort function built in, which it automatically uses for date objects, which is why you only need to parse the attribute value into a date object in the examples in my original response.

[JangoSteve]

One last thought, while it's fresh in my mind. There's a reason that we do custom sorts this way, rather than having e.g. a custom sort function that parses the date on the fly and not having to deal with how we read or write the value to the cached JSON collection.

It'd be slow to parse the date within the sort function itself, as the sort function is called once for each record in the comparison, so we don't want our sort function calling other functions like Date.parse(), which is somewhat explained in the Custom Sort Functions section of the docs.

That's why, to solve the sort issue, we want to fix how we're reading the value into our JSON collection from the table in the first place. This way, we just call the Date.parse() function once for each record when we're reading the initial values in, and then we use a normal sort function after that.

In the future though, I wouldn't mind having a configuration that allows you to specify the default way to read in the value. For example, maybe we could have something like this:

<th data-dynatable-read="date">Date</th>

Which would know to read the text in that column with Date.parse(), and we could have other pre-defined read-types like html, text, integer, float, etc. This would 1) read the data from that column with the specified pre-defined reader function, and 2) cache the HTML value of the column, so that it knows what to write back to the table for each record, rather than trying to write back the parsed value directly from the JSON collection.

[isbkch]

I wanted to answer earlier but I couldn't due to some work obligations.

The <th data-dynatable-read="date">Date</th> was really the function I was looking for when I downloaded DynaTable. You guys do an awesome job here, it was just a shame not providing something as basic as this.

I also wanted to thank you for the time you took to explain the how-to, it helped me a lot building my solutions.

That's what I ended up doing for the reader:

          readers: {
            'date': function(el, record) {
              s    = el.innerHTML.split('-');
              var dateObj = new Date(Number(s[2]), Number(s[1]) -1, Number(s[0])); 
              return dateObj;
            }
          }

and the writer:

          writers: {
            'date': function(record) {
              objDate = record.date;
              d  = objDate.getDate();
              m  = objDate.getMonth() + 1;
              y  = objDate.getFullYear();

              // ensure we have 2 digits for days/months
              d  = (d.toString().length < 2) ? '0' + d : d;
              m  = (m.toString().length < 2) ? '0' + m : m;

              return d + '-' + m + '-' + y;
            }
          }

And I works as a charm.

One more thing, the guessType() function was always returning string for dates and numbers, so I replaced :

     attrType  = a[attr] ? typeof(a[attr]) : typeof(b[attr]),
      type      = types[attrType] || 'number';

By:

          type = 'string';
          if (a[attr]) {
            if (isNumber(a[attr]) || attr == 'date') {
              type = 'number';
            }
          }

PS: yeah I know I'm not that proud of the attr == 'date' :P but I'm sure It's all I need in my case

[brandondrew]

How about <th data-type="date">Date</th> (or, if it's critical to always have 'dynatable' in there to avoid the possibility of a name collision, <th data-dynatable-type="date">Date</th>)?

I think type is more intuitive and descriptive than read, both because the contents of the attribute is a data _type_, and because the name of an attribute should normally be a noun rather than a verb.

Also, I may be missing something, but I don't think you'd need to also have a matching data-dynatable-write attribute. (Or would you?)

[JangoSteve]

You would have to have a matching write attribute, because everything in the DOM is a string. Reading in a string and converting it to a JavaScript Date object, and rendering a Date object as a formatted string are not reversible operations unless you have a very specific string-format for the date in your DOM.

That being said, I've had an idea to add a meta-attribute to all records in the JSON recordset collection called something like dynatable-attributeName-html, that just caches whatever was in the DOM in the first place and then just blindly writes that to the DOM when rendering the record, rather than running the corresponding attributeName value through a write function to render to the DOM.

If we did that, then we wouldn't need a separate write function at all. This would assume though that you're never changing the value of attributeName, as if you did, you would also have to manually change the value of the hidden dynatable-attributeName-html attribute.

[brandondrew]

I understand that there needs to be both a read and write function, but I'm not sure why they would both need to be listed separately in their own attributes. This doesn't seem very DRY: <th data-dynatable-read="date" data-dynatable-write="date">Date</th>

I think this would be clearer/cleaner, and could still trigger both the readers and writers date functions: <th data-dynatable-type="date">Date</th>

Just my 2 cents on API aesthetics. Obviously it wouldn't change the capabilities of Dynatable.

[brandondrew]

As for the option to cache the original values rather than recompute them on every write: I like the idea! I'm assuming it would be easy to turn on or off with a boolean passed to (e.g.) features.valueCache.

[JangoSteve]

I'm not sure I understand how your code would work. The values of data-dynatable-read and data-dynatable-write are referring to named functions or variables referencing a function, meaning your functions for reading and writing would both reference the same function. That wouldn't work, unless your function looked at the argument, did a type-check, and performed one of two completely different functional operations depending on if the input was a Date type (meaning it'd need to write to a string) or a String type (meaning it'd need to parse a date).

[IronRunes]

Just wanted to share my "workaround solution" here. I had a table with lots of columns with dates, so the reader/writer solution didn't totally fit my needs, because, if I understood it correctly, I would've needed to create identical readers and writers for each date column. I used the Alternative Sort functionality and threw timestamps in the hidden column. This kept everything extremely DRY and simple to maintain. While it might not float everybody's boat, I felt it was worth sharing.

[dnsBlah]

@IronRunes , could you please share a simple jsfiddle ? Nevermind :-) @isbkch already provided all info :-)

This works as great as it should

<table id="changeTableOpen" class="nice ticketsTable" cellspacing="0">
        <thead>
            <th data-dynatable-read="date">Created</th>
        </thead>
        <tbody>
            <tr>
                <td>2016-07-02 15:00:00</td>
            </tr>
            <tr>
                <td>2016-07-05 19:00:00</td>
            </tr>
            <tr>
                <td>2016-07-05 15:00:00</td>
            </tr>
                <td>2016-07-05 17:00:00</td>
            </tr>
        </tbody>
</table>

var dynatableClosed = $('#changeTableClosed').dynatable({
        features: {
          search: true,
          paginate: true,
          recordCount: true,
          sorting: true,
          pushState: true
        },
        readers: {
            'created': function(el, record) {
                var datetime = el.innerHTML.split(' ');
                var date = datetime[0].split('-');
                var time = datetime[1].split(':');
                var dateObj = new Date(Number(date[0]), Number(date[1]) -1, Number(date[2]), Number(time[0]), Number(time[1]), Number(time[2])); 
                return dateObj;
            },
        writers: {
            'created': function(record) {
              var objDate = record.created;
              var d  = objDate.getDate();
              var m  = objDate.getMonth() + 1;
              var y  = objDate.getFullYear();
              var h = objDate.getHours();
              var i = objDate.getMinutes();

              // ensure we have 2 digits for days/months
              var d  = (d.toString().length < 2) ? '0' + d : d;
              var m  = (m.toString().length < 2) ? '0' + m : m;
              var h = (h.toString().length < 2) ? '0' + h : h;
              var i = (i.toString().length < 2) ? '0' + i : i;

              return d + '-' + m + '-' + y + ' ' + h + ':' + i;
            }
        }
    }).data('dynatable');
    dynatableClosed.paginationPerPage.set(10);
    dynatableClosed.process();

@JangoSteve you can close this issue