topic Query feature service via REST, where DateTime now is less than some factor in ArcGIS Online Questions

Query feature service via REST, where DateTime now is less than some factor

NickO_Day — Tue, 02 May 2017 18:54:32 GMT

I'm trying to figure out how to filter features that are showing up in a map based on when they were last edited. All of the documentation I have been able to find says that this can't be done in ArcGIS Online or Portal (which is where I'm trying to build the map). What I'd like to do is simply filter the features down to just those that were edited in the last 3 hours, based on the last_edited_date in the data. In SQL, I would just do this:

last_edited_date > DATEADD(hh,-3,GETDATE())

...but this doesn't work. Is there a way to do this via the REST endpoint?

I'm thinking that if I can figure out how to set the where clause correctly, I can take the service URL with the where clause, plug it into a map in AGOL, set a refresh on the layer, and boom... done.

Any thoughts on how to construct the ?

Re: Query feature service via REST, where DateTime now is less than some factor

JakeSkinner — Tue, 02 May 2017 19:19:02 GMT

Hey Nick,

Take a look at the following document:

https://community.esri.com/docs/DOC-8191

Re: Query feature service via REST, where DateTime now is less than some factor

NickO_Day — Tue, 02 May 2017 22:13:41 GMT

Hi Jake,

Thanks for the link! This worked pretty easily... since I'm building this on a local Portal, I had to go into the Server Admin settings to flip the switch on allowing non-standard SQL first (which updated the change into the Portal). Then I restructured the definition query to a BETWEEN instead of what I originally had and it all worked. Here's the new query that works perfectly:

last_edited_date BETWEEN convert(CURRENT_TIMESTAMP()-0.125) AND convert(CURRENT_TIMESTAMP())

Note: The "-0.125" part is 3 hours (or 3/24, or 1/8 of a day). I also changed from GETDATE to CURRENT_TIMESTAMP to avoid a mismatch of timezones between when the feature is edited (since it streams in via GeoEvent and features are automatically tagged in UTC) and the current timezone that I'm in (which is EST, not UTC... CURRENT_TIMESTAMP returns UTC. Just posting this in case someone else needs to reverse engineer the solution.

Thanks again!

Re: Query feature service via REST, where DateTime now is less than some factor

AndresCastillo — Wed, 29 Jul 2020 16:31:47 GMT

Also this helpful blog:

Querying Feature Services: Date-Time Queries