Hello.
Is it possible to use the Frappe REST API to filter by a date on a datetime field?
So, I have this where condition that works to retrieve all records from yesterday in SQL.
WHERE
DATE(d.creation) = DATE_SUB(CURDATE(), INTERVAL 1 DAY)
Can I get the same result in the REST API?
Hi @corinja,
Try it:
{{erpnext.url}}/api/resource/{doctype}?fields=["creation"]&filters=[["creation","=","2024-02-19"]]
References:
I hope this helps.
Thank You!
You can’t use SQL functions but your problem is solvable by just breaking down the problem into two different condition.
DATE(creation) = yesterday ==> creation>=yesterday AND creation<today
?filters=[["creation",">=","2024-02-19"],["creation","<","2024-02-20"]]
Another alternative is timespan filters:
?filters=[["creation","Timespan","Yesterday"]]
Also in general avoid use of CURDATE and similar mysql function. Date in user timezone might be different from date where mysql server is running. Best to just pass exact date in queries.
Hi Ankush. Thanks for your reply. However, the query I am trying to execute is based on a relative date not an absolute date. I need to retrieve the records from yesterday, regardless of when the query is executed.
The timespan filters option sounded like it might be good for my use case, but I got an error from this:
?filters=[["creation","Timespan","Yesterday"]]
FrappeError: Operator must be one of =, !=, >, <, >=, <=, like, not like, in, not in, is, between, descendants of, ancestors of, not descendants of, not ancestors of, previous, next
It seems like if I cannot apply any functions to my query operand (creation) in the REST API I will need to use the database API or write a direct SQL query.
You’re probably using v12? This works fine in all recent versions.
Yeah, sadly we’re stuck on v12 as we haven’t found a working upgrade path yet 
Just to say, I figured it out in my JS code, so it works for me programmatically.
I calculate the date of the dayBeforeYesterday and today and convert both of those to date strings without time, then pass them both in to the filter:
?filters=[["creation",">",dayBeforeYesterday],["creation","<",today]]