topic Re: Query 'yesterdays' edits in ArcGIS Pro Questions

Query 'yesterdays' edits

HeatherScroggins — Tue, 18 May 2021 19:50:27 GMT

I'm trying to figure out a way to query feature classes based on the 'last_edited_date' field so that it shows features that were edited yesterday.

So for today (5/18/2021), anything that has 5/17/2021 in the last_edited_date field regardless of time. And for tomorrow, anything that has 5/18/2021.

I've been trying to use the 'CURRENT_DATE' function in SQL, but that doesn't seem to be working.

Any help is appreciated.

Re: Query 'yesterdays' edits

jcarlson — Tue, 18 May 2021 20:09:30 GMT

It may depend on where your data is stored, but using CURRENT_DATE() does seem to work against a service I tested. Pairing it with EXTRACT, you can easily isolate a single date.

EXTRACT(YEAR FROM last_edited_date) = EXTRACT(YEAR FROM CURRENT_DATE())
AND
EXTRACT(MONTH FROM last_edited_date) = EXTRACT(MONTH FROM CURRENT_DATE())
AND
EXTRACT(DAY FROM last_edited_date) = EXTRACT(DAY FROM CURRENT_DATE()) - 1

Yields:

Re: Query 'yesterdays' edits

HeatherScroggins — Tue, 18 May 2021 20:16:51 GMT

Hmm.....it didn't seem to like that. I just get 'There was an error with the expression.'

As for where my data is stored, it's in a 10.7 enterprise geodatabase on SQL (2014).

Re: Query 'yesterdays' edits

jcarlson — Tue, 18 May 2021 20:20:19 GMT

Ah, I bet that's it. We're on a 10.9 postgres DB.

Try swapping those extracts for 'YEAR(last_edited_date)', etc., and instead of CURRENT_DATE(), try CURRENT_TIMESTAMP(). I can't test it for your setup, unfortunately.

Re: Query 'yesterdays' edits

HeatherScroggins — Tue, 18 May 2021 20:31:46 GMT

Alright, that seemed to do the trick. I ended up with:

YEAR(last_edited_date)=YEAR(CURRENT_TIMESTAMP) AND MONTH(last_edited_date)=MONTH(CURRENT_TIMESTAMP) AND DAY(last_edited_date)=DAY(CURRENT_TIMESTAMP)-1

And that seemed to do the trick.

Thanks!