My solution to date + time sorting

Steven

Firstly I'd like to say that this script is really excellent.

I didn't find the following solution anywhere else on the forum so I thought I'd share it.

It was important to me to display the date/time in the format 16th May 2010 1.07pm.

However there didn't seem to be an easy way to tell DataTables to sort this correctly.

What I did to solve this was immediately before the date/time is displayed in the table cell, I added the date/time again except there were two differences. The first difference was that the format of the date/time was in a very raw format which DataTables can sort numerically as opposed to sorting by date. For the above example, the output would be 20100516130734. To explain from left to right it goes 2010 (year), 05 (month), 16 (day), 13 (hour), 07 (minute) and 34 (seconds). To display like this with PHP, it is:

<?php
echo date("YmdHis", strtotime($date));
?>

The second difference is that I wrapped this date/time with CSS so as not to display it, like this:




I hope this approach helps someone. The only caveat I can think of is that it might not be very SEO friendly but I was doing it for an admin panel so it didn't matter.

Steven

abbottmw

Ive been playing with custom sorting with dataTables. It is possible to write your own sorting to do what you need to do.

I had a date parsing question i posted here a few days ago
http://datatables.net/forums/comments.php?DiscussionID=1933&page=1#Item_2

I played around with your 16th May 2010 1.07pm and came up with this for a custom sorter
Need to do a little more testing with it.

[code]
var months = {};
months["JANUARY"] = "01";
months["FEBRUARY"] = "02";
months["MARCH"] = "03";
months["APRIL"] = "04";
months["MAY"] = "05";
months["JUNE"] = "06";
months["JULY"] = "07";
months["AUGUST"] = "08";
months["SEPTEMBER"] = "09";
months["OCTOBER"] = "10";
months["NOVEMBER"] = "11";
months["DECEMBER"] = "12";

jQuery.fn.dataTableExt.oSort['longdate-asc'] = function(x,y) {
x = (x=="")? 0 : x.split(' ');
y = (y=="")? 0 : y.split(' ');
if(x.length==4){

var yr = x[2];
var mo = months[x[1].toUpperCase()];
var da = parseFloat(x[0]);
var t = x[3].split('.');
var hr = t[0];
var mi = parseFloat(t[1]);
var iLen = t[1].length;
var ampm = String(t[1]).substring(iLen,iLen-2);
if(ampm.toUpperCase() == 'PM')
{
hr = parseInt(hr)+12;
}
if(hr < 10)
{
hr = '0' + hr;
}

if(da < 10)
{
da = "0" + da;
}
if(mi < 10)
{
mi = '0' + mi;
}


x = yr + mo + da + hr + mi;
}

if(y.length==4){
var yr = y[2];
var mo = months[y[1].toUpperCase()];
var da = parseFloat(y[0]);
var iLen = String(t[1]);
var hr = t[0];
var mi = parseFloat(t[1]);
var iLen = t[1].length;
var ampm = t[1].substring(iLen,iLen-2);
if(ampm.toUpperCase() == 'PM')
{
hr = parseInt(hr)+12;
}

if(hr < 10)
{
hr = '0' + hr;
}

if(da < 10)
{
da = "0" + da;
}
if(mi < 10)
{
mi = '0' + mi;
}
y = yr + mo + da + hr + mi;
}
return ((x < y) ? 1 : ((x > y) ? -1 : 0));
};


jQuery.fn.dataTableExt.oSort['longdate-desc'] = function(x,y) {
x = (x=="")? 0 : x.split(' ');
y = (y=="")? 0 : y.split(' ');
if(x.length==4){

var yr = x[2];
var mo = months[x[1].toUpperCase()];
var da = parseFloat(x[0]);
var t = x[3].split('.');
var hr = t[0];
var mi = parseFloat(t[1]);
var iLen = t[1].length;
var ampm = String(t[1]).substring(iLen,iLen-2);
if(ampm.toUpperCase() == 'PM')
{
hr = parseInt(hr)+12;
}
if(hr < 10)
{
hr = '0' + hr;
}
if(da < 10)
{
da = "0" + da;
}
if(mi < 10)
{
mi = '0' + mi;
}
x = yr + mo + da + hr + mi;
}

if(y.length==4){
var yr = y[2];
var mo = months[y[1].toUpperCase()];
var da = parseFloat(y[0]);
var hr = t[0];
var mi = parseFloat(t[1]);
var iLen = t[1].length;
var ampm = String(t[1]).substring(iLen,iLen-2);
if(ampm.toUpperCase() == 'PM')
{
hr = parseInt(hr)+12;
}

if(hr < 10)
{
hr = '0' + hr;
}

if(da < 10)
{
da = "0" + da;
}
if(mi < 10)
{
mi = '0' + mi;
}

y = yr + mo + da + hr + mi;
}
return ((x < y) ? -1 : ((x > y) ? 1 : 0));
};
[/code]

then in your datatables you put the sType in the aoColumns for the date column. This example i tested it on a single column html table.

[code]
$('#mytable).dataTable( {
"bPaginate": false,
"bLengthChange": false,
"bFilter": false,
"bInfo": false,
"bAutoWidth": false,
"bSort": true,
"aoColumns": [
{ "sType": "longdate" }
]

} );
[/code]