Filtering Human readable date

mleppala

Hi all,
I have been playing around with DataTables API for about a month now. I have bee working on getting my table of data and other controls that are outside the table to work with this API such as the search box, page length, and drop-downs filters. I have managed to get everything completed except for the registration date drop-down filter, which I have been trying to get it work for about a week.

The registration date drop-down filter I am using is as follows:

<select id="filter_range" name="filter_range">
      <option value="" selected="selected">- Registration Date -</option>
      <option value="today">today</option>
      <option value="past_week">in the last week</option>
      <option value="past_1month">in the last month</option>
      <option value="past_3month">in the last 3 months</option>
      <option value="past_6month">in the last 6 months</option>
      <option value="past_year">in the last year</option>
      <option value="post_year">more than a year ago</option>
</select>

In the table there is a registration date column that displays human readable dates.

<table id="users-table" class="display users-tbl" cellspacing="0" width="100%">
        <thead>
          <tr>
              <th class="nowrap center"></th>
              <th class="nowrap hidden-phone">
                <a href="#"
                   onclick="return false;"
                   class="column-order hasTooltip"
                   data-order="a.name"
                   data-direction="DESC"
                   data-name="Name"
                   title="&lt;strong&gt;Name&lt;/strong&gt;&lt;br /&gt;Select to sort by this column">
                    Name
                </a>
              </th>
              <th class="nowrap">
                  <a href="#"
                     onclick="return false;"
                     class="column-order hasTooltip"
                     data-order="a.username"
                     data-direction="ASC"
                     data-name="Username"
                     title="&lt;strong&gt;Username&lt;/strong&gt;&lt;br /&gt;Select to sort by this column">
                     Username
                  </a>
              </th>
              <th id="state" class="nowrap center">
                  <a href="#"
                     onclick="return false;"
                     class="column-order hasTooltip"
                     data-order="a.block"
                     data-direction="ASC"
                     data-name="Enabled"
                     title="&lt;strong&gt;Enabled&lt;/strong&gt;&lt;br /&gt;Select to sort by this column">
                     Enabled
                  </a>
              </th>
              <th id="active" class="nowrap center hidden-phone">
                  <a href="#"
                     onclick="return false;"
                     class="column-order hasTooltip"
                     data-order="a.activation"
                     data-direction="ASC"
                     data-name="Activated"
                     title="&lt;strong&gt;Activated&lt;/strong&gt;&lt;br /&gt;Select to sort by this column">
                     Activated
                  </a>
              </th>
              <th id="groupId" class="nowrap">
                  UserGroups
              </th>
              <th id="state" class="nowrap hidden-phone">
                  <a href="#"
                     onclick="return false;"
                     class="column-order hasTooltip"
                     data-order="a.email"
                     data-direction="ASC"
                     data-name="Email"
                     title="&lt;strong&gt;Email&lt;/strong&gt;&lt;br /&gt;Select to sort by this column">
                     Email
                  </a>
              </th>
              <th class="nowrap hidden-phone">
                  <a href="#"
                     onclick="return false;"
                     class="column-order hasTooltip"
                     data-order="a.lastvisitDate"
                     data-direction="ASC"
                     data-name="Last Visit Date"
                     title="&lt;strong&gt;Last Visit Date&lt;/strong&gt;&lt;br /&gt;Select to sort by this column">
                     Last Visit Date
                  </a>
              </th>
              <th id="range" class="nowrap hidden-phone">
                  <a href="#"
                     onclick="return false;"
                     class="column-order hasTooltip"
                     data-order="a.registerDate"
                     data-direction="ASC"
                     data-name="Registration Date" title="&lt;strong&gt;Registration Date&lt;/strong&gt;&lt;br /&gt;Select to sort by this column">
                     Registration Date
                  </a>
              </th>
              <th class="nowrap hidden-phone">
                  <a href="#"
                     onclick="return false;"
                     class="js-stools-column-order hasTooltip"
                     data-order="a.id" data-direction="ASC"
                     data-name="ID"
                     title="&lt;strong&gt;ID&lt;/strong&gt;&lt;br /&gt;Select to sort by this column">
                      ID
                  </a>
              </th>
          </tr>
        </thead>
        <tfoot>
        </tfoot>
        <tbody>
        <tr>
            <td></td>
            <td>Mike</td>
            <td>joe</td>
            <td>
                <a class="btn btn-micro active hasTooltip" href="javascript:void(0);" onclick="" title="" data-original-title="Block this user.">
                    <span class="icon-publish"></span>
                </a>
            </td>
            <td>Active</td>
            <td>Registered</td>
            <td>one@one.com</td>
            <td>1 hour ago</td>
            <td>25 seconds ago</td>
            <td>1</td>
        </tr>
        <tr>
            <td></td>
            <td>Jon</td>
            <td>Teis</td>
            <td>Disable</td>
            <td>Deactive</td>
            <td>Guild Member</td>
            <td>two@two.com</td>
            <td>5 minutes ago</td>
            <td>11 months ago</td>
            <td>2</td>
        </tr>
        <tr>
            <td></td>
            <td>Dan</td>
            <td>Zuk</td>
            <td>Disable</td>
            <td>Active</td>
            <td>Registered</td>
            <td>three@three.com</td>
            <td>5 days ago</td>
            <td>1 year ago</td>
            <td>3</td>
        </tr>
        <tr>
            <td></td>
            <td>Tammy</td>
            <td>kitty</td>
            <td>Enable</td>
            <td>Active</td>
            <td>Registered</td>
            <td>four@four.com</td>
            <td>1 hour ago</td>
            <td>2 hours ago</td>
            <td>4</td>
        </tr>
        <tr>
            <td></td>
            <td>Joan</td>
            <td>tulip</td>
            <td>Enable</td>
            <td>Active</td>
            <td>Registered</td>
            <td>five@five.com</td>
            <td>1 hour ago</td>
            <td>5 years ago</td>
            <td>5</td>
        </tr>
        <tr>
            <td></td>
            <td>Zik</td>
            <td>dope</td>
            <td>Enable</td>
            <td>Active</td>
            <td>Registered</td>
            <td>six@six.com</td>
            <td>1 hour ago</td>
            <td>6 months ago/td>
            <td>6</td>
        </tr>
        </tr>
        </tbody>
    </table>

I tried converting the humanized dates to dates but I was not able to get it to filter. Does anyone have a way that I can filter based on the humanized dates?

Thank you in advance for any help you can provide me.

ML

mleppala

Well I have an update. I was able to find a way do filter the humanized dates. I was able to use SugarJS to convert them to datetime and compare it to the selected filter. After doing some testing I found the method of filtering cause the sort function to only sort on the full data and not what is filtered. I have created a test case to show the issue. I would appreciate any help that can be provided.

tangerine

Presuming that your original data is a Unix timestamp, you could retrieve it in a hidden field and use that field for sorting. See iDataSort.
This assumes that you want your sorted result to be strictly chronological.

mleppala

Tangerine,
Thanks for the reply. The original data we be coming from a database so it will have to be converted. So I will have to create a hidden field for each record? Do an hidden field would seem like a band-aid then a fix. Maybe I am just over thinking what you suggesting to use, do you happen to have an example of it actual usage?

HPB

Put the $.fn.dataTable.ext.search.pop(); at the start of the event listener just before the $.fn.dataTable.ext.search.push( instead of at the end after drawing.
Now you're deleting the search values after drawing and on a new draw (sort, search etc) you're data will no longer be filtered with the dropdown values.
live.datatables.net/ziqeruhe/3/edit

On a sidenote, using the real Unix timestamps would be a cleaner solution. You wouldn't even need a hidden column.
Put in the timestamp in the column and use columns.render to display human readable dates.
This would fix the sorting of the human readable dates as well, as you can use the Unix timestamps to sort on, instead of the human readable date.

tangerine

So I will have to create a hidden field for each record?

My apologies, @mleppala, I should have said "column" not "field".
The documentation for iDataSort has example code.

Alternatively, @HPB's "render" would be an equally effective method.

mleppala

@HPB Thanks for the post and the fix. I moved the pop method above the push method and it working sorting based on the the displayed rows. Reading up on columns.render I can see the benefit to using it.

@tangerine Np from what I read about the iDataSort it was talking about columns not fields so I was hoping that you did meant column.

Since the human readable dates are only on a view page I was going to say that I should not need to use the methods you both stated but while testing more I found other issue with the date column when the sorting. The issue I am not having is that when I sort the date column it is not actually sorting by the correct time rather the text. This would mean that I will need to decide on which of the two options that you presented me to use since it looks like either one will fix the issue.

Thanks to the both of you for the help.