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.
Solved! Go to Solution.
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!
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:
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).
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.
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!