Confusing results when filtering a Date field using REST API

Question

I have a SharePoint online site collection which use London time zone:-

and i am using a service account which use the same time zone inside its profile:-

inside a list I have a field named Expiration date of type Date only:-

now using the list forms >> when I add a new item using the Calendar and I set the Expiration date to let say 14 May 2023 >> the date will be saved/viewed as 14 May 2023 inside the UI (list views) >> but will be saved as 13 May 2023 23:00:00 inside the rest API (when I query it is using the rest API), as follow:-

Now i think i know the reason for this, i that SharePoint stores the Date based on UTC which is minus one hour from London time. but the weird thing is that if i write this Filter inside the rest Api I will get the item:-

/_api/lists/getbytitle('Certifications')/items?$filter=(ExpirationDate ge '2023-05-13T00:00:00Z') and (ExpirationDate le '2023-05-13T23:59:59Z')

and also if i write this query i will also get the same item:-

/_api/lists/getbytitle('Certifications')/items?$filter=(ExpirationDate eq '2023-05-14')

so not sure if this item has its expiry date on 13 May or on 14 May from SharePoint REST API point of view? also can i assume that if i pass the time inside the API filter then SharePoint will not perform any conversion to UTC?

Answer 1

This is because in the first filter, you are specifying that the date is in UTC format by adding the Z (Zero time zone) at the end. In the second filter, you are passing a generic date and therefore SharePoint is assuming that the date is in the locale for that SharePoint site.