Filtering by date Problem

[svhtamil]

I am just a beginner, i have deployed budibase in digital ocean droplet. My problem is i have a table with data sorted by date, in a page i am trying display the table through a data provider with date filter i have set the bindings as greater than or equal to for from and lesser than or equal to for to date, the filtered resultant table is showing data from date correctly but to date is showing 1 day less. for eg

from 3/5/24 to 4/5/24 if i apply filter the data on table is showing 3/5/24 only (see attached pic), i have already enable ignore time zones in tables in data page and in date picker options also ignore time zones is set. Please help me.

[mikesealey]

Hi @svhtamil

I've been looking into this for you. It does seem to be something of a drawback in the way that we are comparing dates. I think it's specifically around the times that are attached to the dates that we're looking for. Even when, in the data section, we select "Date Only", we seem to set a time of 01:00. We can see this by creating some rows while the column is set to "Date Only" and then switching it back to also include the time.

Even when that column is set to "Date Only", it seems to still hold that 01:00 time, which we can see by setting our Date Picker components to Show Time, and making the query with a start date and time to be 00:30, which will then show values

However, knowing this, we can use it to our advantage by specifying times when we chose the From and To dates of our range. Adjust the settings of your Date Picker so that Show Time is not checked. Now in your Table Filter section we can force these filters to work from the very beginning of the start-date to the very end of the end-date.

We'll use a little bit of the JavaScript Date Constructor (read more here). In short, we're taking the date specified, and attaching a time to it - the first minute of the day in the "from" filter, and the last minute in the day in the "to" filter.

Copied and pasted here for ease
return Date($("New Form.Fields.Start Date") "00:00:00")
return Date($("New Form.Fields.End Date") "23:59:59")

That should help get you around the issue you're facing. I'm going to write up an enhancement to address the root of the issue though.

[svhtamil]

Thank you for the guidance , i applied your workaround but unfortunately i am getting error while executing js

Please help.

[svhtamil]

With the help of chatgpt i used the below code and its working perfectly fine now. Thanks

let dateStr = $("New Form 1.Fields.Date");

if (dateStr) {
let dateTimeStr = dateStr + "T00:00:00";
let dateObj = new Date(dateTimeStr);

if (!isNaN(dateObj.getTime())) {
return dateObj.toISOString();
} else {
return null;
}
} else {
return null;
}