nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Query 'yesterdays' edits

CityofRichardsonGIS

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.

Find more posts tagged with

Content Management

Desktop

Accepted answers

CityofRichardsonGIS

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!

All comments

CityofRichardsonGIS

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!

jcarlson

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.

CityofRichardsonGIS

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

jcarlson

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:

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.