Filter DateTime in List or Library control

[StepanS (Steo)]

Hello guys,

I hope someone will help me to make it work

Usecase: User selects a company car for a rent. Then he selects date from and date to. So I want to rent a car titled "Škoda Octavia" and make a reservation from "today 9:00" to "today 17:00".

This is the design of the form:

What is the purpose - When the car is rented for today from 9 to 5 and none of other reservations is within the interval, then it is ok.

If I rent a car from 3AM to 6AM and my colleague from 9AM to 5PM - all good.
When I rent a car from 9AM to 5PM and my colleague needs to rent a car from 8 AM to 10AM/11AM, the filtration does not work to show him, my reservation is within the interval and he cannot make a reservation.

My code:

   const from = new Date(fd.field("dtFrom").value);
    const to = new Date(fd.field("dtTo").value);
    console.log("From:", from, "To:", to);

    const fromUtc = from.toISOString();
    const toUtc = to.toISOString();

    console.log("FROM UTC", fromUtc, "TO UTC", toUtc);
    const caml = `
         <And>
        <Eq>
          <FieldRef Name="vVehicle" LookupId="TRUE" />
          <Value Type="Lookup">${vVehicle.LookupId}</Value>
        </Eq>
        <And>
          <Lt>
            <FieldRef Name="dtFrom" />
            <Value Type="DateTime" IncludeTimeValue="TRUE">${toUtc}</Value>
          </Lt>
          <Gt>
            <FieldRef Name="dtTo" />
            <Value Type="DateTime" IncludeTimeValue="TRUE">${fromUtc}</Value>
          </Gt>
        </And>
      </And>
  `;

    list.filter = caml;
    list.refresh();

1. Filtration by vehicle - absolutely OK
2. Date Time is weirldy filtered - maybe my condition is wrong, but I need to find overlapping time.

GOAL: When there is an existing reservation from 9AM to 5PM, everytime my colleague comes to this form and select whatever date is within the interval, he should see "My reservation" in the list or library control:

THERE IS A RESERVATION IN THE LIST FROM 9AM TO 5PM IN THE LIST FOR THE SELECTED CAR. Now I am trying to add another reservation for the same car in the same day.

1. FORM:
   

   

2. List or Library filtered by code: (None) - but I should see the record, because 11AM is overlapping with 9AM to 5PM.
   

   

3. FORM:
   

   

4. List or Library filtered by code (changed the time to 11:30)
   

   

So, it looks like Timezone could be the issue but I convert the time to UTC and when I use PnP JS to filter the data, it kinda works.

I would like to do it this way, probably th ebest approach to filter the data in list or library control, or I have another idea to filter data with PnP JS and returned IDs to iterate and filter in the list or library (but I do not want to do it this way).

Your help is appreciated
Thank you
Stepan

[StepanS (Steo)]

This adjustment works, but is incorrect due to timezone:
Currently +2 hours CEST in our country.

• from - variable
• to - variable

    const fromUtc = new Date(from.getTime() + 2 * 60 * 60 * 1000).toISOString();
    const toUtc = new Date(to.getTime() + 2 * 60 * 60 * 1000).toISOString();

• With this setup, the filtration works like a charm. Even though the date and time is saved as UTC, UTC converted like:

x.toISOString()

Does not return data and always probably checking the timezone if it fits my browser settings.

Stepan