search

planningcenter / developers

Planning Center API docs and support
https://developer.planning.center/docs/
85 stars 8 forks source link

Search by Date in Api Not functioning as expected #1210

Closed localpath closed 3 months ago

localpath commented 3 months ago

Related Product Which product is this question related to? Giving

Describe the question I'm building an integration that will rely heavily on querying Donations by their received_at and created_at dates. I'm unfortunately having issues pulling any date that is an exact match with just = and using gte with lte requires me to add minutes before and after the data as it exists in the actual dataset. This is very imprecise as you can imagine but raises the larger question what exactly does searching by date with just = do? Does it work only for certain intervals ie year, month, day, hour, minutes, seconds? Does seconds or minutes not work? The documentation is a little thin on these more specific questions.

Example Donation Object I'm trying to query for by received_at

{
            "type": "Donation",
            "id": "242212414",
            "attributes": {
                "amount_cents": 20520,
                "amount_currency": "USD",
                "completed_at": "2024-07-07T04:04:08Z",
                "created_at": "2024-07-08T18:49:22Z",
                "fee_cents": 0,
                "fee_currency": "USD",
                "payment_brand": null,
                "payment_check_dated_at": null,
                "payment_check_number": null,
                "payment_last4": null,
                "payment_method": "card",
                "payment_method_sub": null,
                "payment_status": "succeeded",
                "received_at": "2024-07-08T18:49:19Z",
                "refundable": true,
                "refunded": false,
                "updated_at": "2024-07-08T18:49:22Z"
            },
 }

What have you tried that worked? Broadening my query by several minutes for start and end (not completely understanding exactly how)

https://api.planningcenteronline.com/giving/v2/donations
?per_page=100
&offset=1
&where[received_at][gte]=2024-07-08T18:46:00Z
&where[received_at][lte]=2024-07-08T18:50:00Z

Seems like I can use the gte/lte if I got 3 minutes and 9 seconds before and the lte seems to accept the exact time as a second

What have you tried that didn't work? Searching by received_at on a certain datetime

https://api.planningcenteronline.com/giving/v2/donations
?per_page=100
&offset=1
&where[received_at]=2024-07-08T18:49:19Z

Additional context

image

image

I have..

glosie commented 3 months ago

Hi, @localpath!

You're right; the documentation could use some improvements in this area. We'll be sure to follow up on that!

what exactly does searching by date with just = do?

It should return resources with a received_at occurring any time that day. Also note: though not explicitly stated in the docs, date values are assumed to be in the organization's time zone.

Looking at your examples, I believe the behavior you're seeing might result from the offset=1 param in the requests. If you're expecting a single result, it may be getting unintentionally excluded 🤔.

I'd expect the results of this request to include the example donation above (though if there are more than 100 donations with a received_at on that date, it may be on a subsequent page):

https://api.planningcenteronline.com/giving/v2/donations
?per_page=100
&where[received_at]=2024-07-08

I hope that helps!

localpath commented 3 months ago

😭 You're right! it was just the offset when I was querying by the exact datetime to the second. Thanks for the help! I think this will make it pretty easy for me knowing I can query by the ISO date I have vs what's in your dataset for exact matches. Will help tremendously!

I think the offset 1 was causing the issue when I was having to query a few minutes ahead of my expected time bc another record was popping up with it making my expected record then show.

image