Query 'yesterdays' edits

549
4
Jump to solution
05-18-2021 12:50 PM
Labels (2)
HeatherScroggins
Occasional Contributor II

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.

0 Kudos
1 Solution

Accepted Solutions
HeatherScroggins
Occasional Contributor II

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! 

View solution in original post

4 Replies
jcarlson
MVP Notable Contributor

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:

jcarlson_1-1621368563235.png

 

- Josh Carlson
Kendall County GIS
0 Kudos
HeatherScroggins
Occasional Contributor II

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).

0 Kudos
jcarlson
MVP Notable Contributor

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.

- Josh Carlson
Kendall County GIS
HeatherScroggins
Occasional Contributor II

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!