Issue With DataTables Date Range Filter

nport

http://live.datatables.net/kilayuce/5/edit

I implemented a date range filter using DataTables Date Range Filter: https://datatables.net/extensions/datetime/examples/integration/datatables.html

It works perfect except for one case: When you put a date in the filter it will not show records with equal dates. For example if you go to the test case, and put in the start date as August 11th 2013 no records will show up even though there is a record with date August 11th 2013. If you then change the start date to August 10th 2013 the record will appear.

This issue only seems to occur the first time you input a date, after that it seems to work as expected. How do I fix this?

kthorngren

Maybe I'm not understanding. There aren't any dates with August 11th 2013 in the table. I changed one of the rows with this date. I type in 08/11/2013 and the row with that date shows along with a couple others with later dates.
http://live.datatables.net/kilayuce/217/edit

Let me know what I'm missing.

Kevin

nport

@kthorngren my bad... I believe I included an incorrect link to test case.

This is the one I meant to link: http://live.datatables.net/topuhayo/6/edit

nport

@kthorngren if you use the test case I just linked the issue is if you put a start date in the filter if there are any records with that exact date (or less) they will not show up, but after you have changed the values a couple times it will work.

sinfuljosh

I am not able to test in the live datatable from my phone. But it would like there’s an overlap based off of the time aspect of the DateTime.

If you can zero out the time portion from the min/max then you can ensure that it grabs that entire day.

(Anytime I am stuck doing date ranges, I make sure it’s only looking at the Date and then adjust the min/max to subtract/add a day and use < and > in the filtering (no <= and >= ).

This ensures that the query grabs every item within the selected date range.

I hope this helps and I’ll check to see if still need help when I’m back at my computer.

kthorngren

@sinfuljosh is correct. Using the DateTime picker seems to set the time to the local time when not choosing it from the input. For example: 2013-08-11T07:09:28.000Z.

Not sure how to zero out the time. @allan or @colin can comment. Also I wonder if the DateTime picker should automatically zero out the time if only choosing the date.

Kevin

nport

@sinfuljosh @kthorngren okay thank you for the insight, I will see if I can figure out how to zero out the time.

If either of you find a solution I would appreciate any help

nport

@sinfuljosh Did you get a chance to check it out? I have had no luck, I just keep running into more problems. I was able to remove the time using a couple different methods such as .toLocaleDateString(), but I ran into new problems that I believe have to do with timezone issues. The dates in the table and filter input are sometimes off by one day, which I think may be causing the issue

sinfuljosh

I am actually looking at it right now. And that is what it is doing.

The two input fields return Dates with Times attached.
"Min: Mon Jan 31 2022 18:00:00 GMT-0600 (Central Standard Time)"
"Max: Sat Feb 19 2022 09:36:15 GMT-0600 (Central Standard Time)"

The data in the table returned a date with the hours set to 0.
"Date: Fri Jun 01 2012 00:00:00 GMT-0500 (Central Daylight Time)"

Working on the best way to address this, since i still suck at programming. (i suck at javascript)

sinfuljosh

If someone that is familiar with JS can help out on this. On my setup we used timestamps so it was easier to deal with. I used moment.js to convert it to timestamp and it just filtered like a number. But the strange thing I noticed on your link is that that the time is off.

I think there is a bug in editors DateTime, if I change the format to include time. The input value and the console log show two different values.

nport

@sinfuljosh Okay I came up with something, definitely not the prettiest solution but it works. I ended up not even zeroing out the time component of the date object, I just just changed the inputs for the start and end date to be type="date" from type="text" like so:
<input type="date" id="min" name="min">
I then initialized the date column and the 2 inputs all as new Date() objects.

Here is the link to my updated code: http://live.datatables.net/topuhayo/12/edit

sinfuljosh

@nport Thanks for that update. I dont know why I used what you posted above and was able to use datatables DateTime input. What was messing me up earlier was it not liking the format used in the input, changing that and using your if null got it working.

Original: http://live.datatables.net/topuhayo/6/edit
Working: http://live.datatables.net/polaloto/1/edit

Changed original lines 6-7 to:

        let min = ($('#min').val() == '') ?
            null :
            new Date( $('#min').val() ).setUTCHours(0,0,0,0);
        
         let max = ($('#max').val() == '') ?
            null :
            new Date( $('#max').val() ).setUTCHours(23,59,59,999);

Changed original lines 29-35 to :

    // Create date inputs
    minDate = new DateTime($('#min'), {
        format: 'MM/DD/YYYY'
    });
    maxDate = new DateTime($('#max'), {
        format: 'MM/DD/YYYY'
    });

The one further I would look into is top edit so that it checks for a valid date and if its not, then return null.
UPDATE: If you are using moment.js you can use their isValid to check for a valid date or else return null (stops the datatable from filtering on invalid dates)

Updated from original lines 6-7:

        let min = moment($('#min').val()).isValid() ?
            new Date( $('#min').val() ).setUTCHours(0,0,0,0) :
            null;
        
         let max = moment($('#max').val()).isValid() ?
             new Date( $('#max').val() ).setUTCHours(23,59,59,999):
             null;
        var date = new Date( data[4] );

nport

@sinfuljosh awesome thank you for the update as well! This issue took me a lot longer than I would like to admit lol, thank you for your help I really appreciate it it seems like everything is working as you would expect now

sinfuljosh

It was puzzling to me also originally due mainly to the date format I think. But when you changed the formatting and was able to generate the new Date(). It all clicked.

Now I need to find out how to file a bug report because the date values that the input shows and the dates that the console reports are off. (I think one is reporting UTC and one is not. )