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.
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
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.
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.
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
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 :-)
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.
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);
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]
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.
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.
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.
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
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.
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?
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;
}
);
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?
Replies
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
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
[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.
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
[code]
$('.datepicker').datepicker(
{ onSelect: function(date) {
oTable.fnDraw();
},
... [rest of initialization]
});
[/code]
The keyup event is never fired
Is there a live demo of this using a jquery date range picker?
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
Regards,
Allan
<!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();
} );
} );
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]
http://live.datatables.net/etewoq/88/edit
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.
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!
is fireing the event to datatable corectly =>ajax update table is called, but
https://github.com/eternicode/bootstrap-datepicker
doesn't
https://github.com/hemantrai88/datatables-date_range_filter
It is really simple to customize this function to make it work for different date-formats.
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.
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;
}
);
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.
That is awesome. Way to go carlose!
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?
you can also try my yadcf plugin it got range_date filter and 8 more types...
http://yadcf-showcase.appspot.com/DOM_source.html