Filter Date range

mizpro

I have a column of dates which has this format mm/dd/yyyy. I want to be able to filter based on a date range. Is this possible?

allan

Hi mizpro,

It certainly is - DataTables presents an API for custom filtering which can be used for exactly this kind of thing. I've got an example up for range filtering ( http://datatables.net/1.5-beta/examples/api/range_filtering.html ) although it only does floating point numbers, rather than dates. However, it should be fairly easy to convert this basic idea to parse a date and then check if it is in your filtering range.

Hope this helps,
Allan

bahallou

Hi mizpro,
I was wondering if you found a solution to your problem date?
I can not make a min/max per day despite the example of Allan.
I know I have to use the Date.parse () but I do not know how to apply.
please help
Thanks

stormlifter

It would be worth it to make a dedicated filtering system that can handle dates with a date-picker and such. I'd be willing to put some hours forward to make that happen since I need it for a project. All we'd really need to do is add syntax for ranges into the code. " xxxx - xxxx" would filter between that range. Then we could use existing range pickers.

stormlifter

I went ahead and wrote one for filtering using a date range provided in a single input field.
[code]
$.fn.dataTableExt.afnFiltering.push(
function( oSettings, aData, iDataIndex ) {
// "date-range" is the id for my input
var dateRange = $('#date-range').attr("value");

// parse the range from a single field into min and max, remove " - "
dateMin = dateRange.substring(0,4) + dateRange.substring(5,7) + dateRange.substring(8,10);
dateMax = dateRange.substring(13,17) + dateRange.substring(18,20) + dateRange.substring(21,23);

// 4 here is the column where my dates are.
var date = aData[4];

// remove the time stamp out of my date
// 2010-04-11 20:48:22 -> 2010-04-11
date = date.substring(0,10);
// remove the "-" characters
// 2010-04-11 -> 20100411
date = date.substring(0,4) + date.substring(5,7) + date.substring( 8,10 )

// run through cases
if ( dateMin == "" && date <= dateMax){
return true;
}
else if ( dateMin =="" && date <= dateMax ){
return true;
}
else if ( dateMin <= date && "" == dateMax ){
return true;
}
else if ( dateMin <= date && date <= dateMax ){
return true;
}
// all failed
return false;
}
);
[/code]
It will take in arguments like:
[code]
2010-03-01 - 2010-03-31
[/code]
or even
[code]
2010-03-01
[/code]
This code will handle the case of whatever shape your data is in as long as it takes the same number of characters. So mm-dd-yyyy will work fine in this system.

You can change the delimiter to whatever you want, just remember to adjust the substring code.

An example event handler is:
[code]
$('#date-range').keyup( function() { oTable.fnDraw(); } );
[/code]


Those of you using jQuery can edit the first line after the function with
[code]var dateRange = $('#date-range').attr("value");[/code]


If anyone finds any errors or has suggestions let me know since I did this when I was very tired.

stormlifter

Updated code to be mo' betta.

katrukhin

Good afternoon!
If you will not complicate, could you to set the link on an example what to see as properly completely to write a code for a filtration of dates

maragues

Hi, just to update stromlifter's code, when using Datepicker to input dateranges you need to rely on the onSelect event inside DatePicker

[code]
$('.datepicker').datepicker(
{ onSelect: function(date) {
oTable.fnDraw();
},
... [rest of initialization]
});
[/code]

The keyup event is never fired

studioleland

@Allan

Is there a live demo of this using a jquery date range picker?

allan

Hi studioleland,

Here is an example I've just put together. Its a little limited as it just simply filters on the date format from the date picker as a string (i.e. the format from the date picker must match what is in the table - a mapping could be done if you need it in your application): http://live.datatables.net/etewoq/edit#javascript,html

I've also added a keyup event handler so the filter can be cleared - again depending upon your application this might need modified, but will hopefully give you a good start with it :-)

Allan

studioleland

This is good stuff. I'll post my results shortly.

allan

Here is a modification of my mockup which does a min / max filter: http://live.datatables.net/etewoq/4/edit . It uses afnFiltering like stormlifter's example above, but it is slightly optimised in that it stores the parsed date string and works with that for the filtering. Note that here you just call fnDraw rather than fnFilter since afnFiltering is applied automatically on a draw.

Regards,
Allan

studioleland

Good stuff. Love the support.

ssoosi

Hi guys needs some my date range does not seem to filter like it should any help please

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">







@import "/media/css/demo_page.css";
@import "/media/css/demo_table_jui.css";














Min date:
Max date:




id
Promo
datesubbed



<?php
$username="root";
$password="";
$database="carsubexporter";

mysql_connect(localhost,$username,$password);
mysql_select_db($database) or die( "Unable to select database");
$query="SELECT * from carsubs2 ORDER BY datesubbed DESC";
$result=mysql_query($query);

$num=mysql_numrows($result);

mysql_close();

echo "Database Output

";

$i=0;
while ($i < $num) {

$id=mysql_result($result,$i,"id");
$promoid=mysql_result($result,$i,"promoid");
$datesubbed=mysql_result($result,$i,"datesubbed");
?>


<?php echo $id; ?>
<?php echo $promoid; ?>
<?php echo $datesubbed; ?>


<?php
$i++;
}
?>





var minDateFilter;
var maxDateFilter;

$.fn.dataTableExt.afnFiltering.push(
function( oSettings, aData, iDataIndex ) {
if ( typeof aData._date == 'undefined' ) {
aData._date = new Date(aData[1]).getTime();
}

if ( minDateFilter && !isNaN(minDateFilter) ) {
if ( aData._date < minDateFilter ) {
return false;
}
}

if ( maxDateFilter && !isNaN(maxDateFilter) ) {
if ( aData._date > maxDateFilter ) {
return false;
}
}

return true;
}
);

$(document).ready( function() {
var oTable = $('#example').dataTable( {
"bJQueryUI": true
} );

$( "#datepicker_min" ).datepicker ( {
dateFormat: 'yy-mm-dd',
"onSelect": function(date) {

minDateFilter = new Date(date).getTime();
oTable.fnDraw();
}
} ).keyup( function () {
minDateFilter = new Date(this.value).getTime();
oTable.fnDraw();
} );

$( "#datepicker_max" ).datepicker( {
dateFormat: 'yy-mm-dd',
"onSelect": function(date) {
maxDateFilter = new Date(date).getTime();
oTable.fnDraw();
}
} ).keyup( function () {
maxDateFilter = new Date(this.value).getTime();
oTable.fnDraw();
} );
} );

janrusselcalachan

For what is this? Can you provide a code that will accept two values...
the "from" and "to" DATES....


[quote]// parse the range from a single field into min and max, remove " - "
dateMin = dateRange.substring(0,4) + dateRange.substring(5,7) + dateRange.substring(8,10);
dateMax = dateRange.substring(13,17) + dateRange.substring(18,20) + dateRange.substring(21,23);[/quote]

technoicon

I wanted a few extra things, like auto load only todays date. also removed the search and put a fake header so it looks like date is were it should be.

http://live.datatables.net/etewoq/88/edit

technoicon

in the example i was able to swap out input type="text" for input type="date", but this didnt work on my website?

JohnEzlab

I'm really confused as to what is the best approach to use for date filtering.

If you view the examples (http://datatables.net/plug-ins/filtering#functions) it mentions to include dataTables.rangeFilter.js and refers to a solution by guillimon (http://datatables.net/forums/discussion/537/fnfilter/p1).

Whereas in the current post rangeFilter.js does not seemed to be used and guillimon's solution is not mentioned.

Following the example on this page I am trying to use the min and max dates in a 'dd/mm/yyyy' format. The date displays correctly in both the datatable and the datepicker, yet the table is not being filtered. No errors are displayed in firebug.

I would greatly appreciate recommendations on the best approach here.

Thanks.

JohnEzlab

Has anyone achieved the date filter with the dd/mm/yyyy date format?

timwilson

Does anybody have any suggestions on saving the date range when the page reloads similar to the search box?

I understand this is outside of the bStateSave, but there are ways to store search criteria in the oSettings which survive page reloads.

Here is my actual question with more information on what I am trying to do:

http://datatables.net/forums/discussion/12598/save-date-range-filtering-on-page-reload#Item_1

Thanks!

jb008164

Just curious. Are conditions on lines 24 and 21 not checking the same exact condition with the same return of true? Please tell me I am missing something here.

roxyza

https://github.com/vitalets/bootstrap-datepicker
is fireing the event to datatable corectly =>ajax update table is called, but
https://github.com/eternicode/bootstrap-datepicker
doesn't

deckoff

What is the format of the column that is being filtered. I mean, the input is obviously yyyy-mm-dd. On the other hand, the format of date of the column is not specified - I suppose it is yyyymmdd, cos I cannot filter anything with the code given.

hemantrai

If you want to filter a DataTable based on a date-range, you can try this function :

https://github.com/hemantrai88/datatables-date_range_filter

It is really simple to customize this function to make it work for different date-formats.

JohnEzlab

Is there a way of achieving this with using a column name rather than a column index? For example, if I have a standard column name such as "sort-date" but the column could be in different positions on different tables (ie sometimes an index of 1, other times an index of 5 etc), would it be possible to grab the column's index by the column's name and then sort on that?

cabynum

JohnEzlab you can do something like this to identify the index:

var table = $('#tableName').dataTable();
var length = table.fnSettings().aoColumns.length;
for(var i = 0; i < length; i++){
if(table.fnSettings().aoColumns[i].nTh.innerHTML == 'headerValue'){
return i;
}
}

You could do it at the beginning of every filter and set a variable to the value and use the variable.

cabynum

I updated the function to take in 2 params from different fields. I also used the date object so that the formats "can" be different. Date objects compare pretty well in my experience.

This filtering will fire on any filter event. So if you filter directly on the date column but you pull your start and end dates from a different place then your filter directly on the column will get replaced by this filter call.

$.fn.dataTableExt.afnFiltering.push(
function( oSettings, aData, iDataIndex ) {
// "date-range" is the id for my input
var dateRange = $(fromDateField).attr('value') + '-' + $(toDateField).attr('value');

// parse the range from a single field into min and max, remove " - "
//dateMin = dateRange.substring(0,4) + dateRange.substring(5,7) + dateRange.substring(8,10);
// dateMax = dateRange.substring(13,17) + dateRange.substring(18,20) + dateRange.substring(21,23);
dateMin = $(fromDateField).attr('value');
dateMax = $(toDateField).attr('value');

dateMin = new Date(dateMin);
dateMax = new Date(dateMax);

// 2 here is the column where my dates are.
var date = aData[2];
date = new Date(date);



// run through cases
if ( dateMin == "" && date <= dateMax){
return true;
}
else if ( dateMin =="" && date <= dateMax ){
return true;
}
else if ( dateMin <= date && "" == dateMax ){
return true;
}
else if ( dateMin <= date && date <= dateMax ){
return true;
}
// all failed
return false;
}
);

carlose

In case someone needs to filter by date range, here´s a working example with datepickers: http://carlose.pessoal.ws/datatables/examples/date_range.html.

This example uses two inputs, one for min date and one for max date.

Thanks to hemantrai and stormlifter for the initial codes I worked on.

mRender

That is awesome. Way to go carlose!

becstra

Thank you @cabynum for your response - that's exactly what I needed....However...when the table first loads, nothing displays because there is no date range set. How do I get all results to display?

daniel_r

you can also try my yadcf plugin it got range_date filter and 8 more types...
http://yadcf-showcase.appspot.com/DOM_source.html