My solution to date + time sorting
My solution to date + time sorting
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
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
This discussion has been closed.
Replies
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]