topic Re: Querying AGOL feature service by date range in ArcGIS Pro in ArcGIS Pro Questions

Querying AGOL feature service by date range in ArcGIS Pro

deleted-user-ka73FBRGJUOO — Mon, 01 Oct 2018 15:56:53 GMT

I have a feature service that is being populated by our field workers using Survey123.

I have created a map series in ArcGIS Pro 2.2.0 that references said feature service, with the goal of creating a multi-page report, using dynamic text and images.

Each week I want to export a PDF of the prior week's entries. The map series, naturally, is cumulative...with a page for every single point created since the very first one.

Is there a way to apply a definition query to the feature service so that it only shows the most recent week's worth of points? I've tried a few different approaches but have hit a brick wall. The feature service has both the standard CreationDate and EditDate fields, as well as a user-inputted date field.

Re: Querying AGOL feature service by date range in ArcGIS Pro

JoshuaBixby — Mon, 01 Oct 2018 18:13:32 GMT

Wait until ArcGIS Pro 2.3 and try again.

#BUG-000111737 Applying Select By Attributes on date fields of hosted feature services fails in ArcGIS Pro.
Status: Implemented
Version Fixed: 2.3

Re: Querying AGOL feature service by date range in ArcGIS Pro

JoshuaBixby — Tue, 02 Oct 2018 16:57:16 GMT

I just got an e-mail from Esri this morning saying the status of this bug has changed to "Not in Current Product Plan." This is a new one for me, not the actual status, but having a bug that is already fixed somehow changing to not going to be fixed. Here is Esri's explanation:

This is a known limitation. To avoid this, we would recommend a best practice of using the BETWEEN SQL operator to perform the query using SQL rather than using ‘=’ in the query builder. This will ensure accuracy and reliability. For more infomration, please contact Support Services. Use the example below adding a second before and after the time you are querying for, ensuring that the first timestamp is precedes the second one. last_edited_date BETWEEN TIMESTAMP '2018-09-24 20:10:53' AND TIMESTAMP '2018-09-24 20:10:55'

Re: Querying AGOL feature service by date range in ArcGIS Pro

KoryKramer — Tue, 02 Oct 2018 18:48:34 GMT

Hi Joshua. You're right that the incorrect status led to confusion - sorry, but it looks like there was simply a mix up in marking the status the first time. It was incorrectly marked as In Product Plan.

The latest update and explanation is correct.

Re: Querying AGOL feature service by date range in ArcGIS Pro

TimothyMichael — Tue, 30 Oct 2018 12:02:54 GMT

Kory,

Is the syntax different if querying a feature service coming from ArcGIS Enterprise? I tried the recommended BETWEEN syntax in ArcGIS Pro and get a message that there was an error with the expression.

I was able to get a simpler query to work (DATERECEIVED BETWEEN '2018-01-01 00:00:00' AND '2018-12-31 12:59:59') querying against the REST endpoint directly, but that doesn't work in Pro either.

I have standardized queries disabled for ArcGIS Server.

Thanks,

Tim

Re: Querying AGOL feature service by date range in ArcGIS Pro

KoryKramer — Tue, 30 Oct 2018 12:53:36 GMT

I was seeing what you're seeing when working with a hosted feature service from ArcGIS Online, setting a definition query in Pro, when the ArcGIS Online organization wasn't using the standardized SQL only security setting. With that setting checked on for the organization, the query works. I logged a bug [BUG-000117318: Querying on a date field of a hosted feature service fails when the ArcGIS Online organization security setting to ‘Allow only standard SQL queries’ is unchecked.] From your results, looks like it isn't specific to ArcGIS Online.

Can you enable standardized SQL queries on the server and try in Pro again? If you're able to say, is there a business need for standardized queries to be disabled?

Re: Querying AGOL feature service by date range in ArcGIS Pro

TimothyMichael — Tue, 30 Oct 2018 16:01:44 GMT

Kory,

Thank you for the reply. I reset the standardized queries setting to true and still get the same error in ArcGIS Pro. I tried both versions of the query I posted in the last post, with and without 'TIMESTAMP.'

The reason why I originally disabled standardized queries was to test taking certain features offline with Collector. For the same project, I have a need to have users take features offline that meet date-based criteria (i.e. some action needs to be taken 'today'). On the ArcGIS Enterprise / Server side I first applied a definition query to the layer (TESTRESULT <> 'New' AND SCHEDULEDDATE LIKE CONCAT(CONVERT(date,GETDATE()), '%') in ArcGIS Pro and then published the service. This seemed to work OK for the service, but Collector would display an error when trying to take the data offline - I think the error referenced the Sync Service. As a test I disabled the standardized queries setting and re-tested the sync in Collector, which worked, somewhat - the features were available in the offline map but changes made to those features would not synchronize back to the service.

Let me know if there's anything else I can try. Thanks!

--Tim

Re: Querying AGOL feature service by date range in ArcGIS Pro

TimothyMichael — Tue, 30 Oct 2018 16:31:31 GMT

Kory,

My mistake - I forgot to restart the ArcGIS Server service after updating the standardized queries setting. I restarted it and now the query I posted in my original screenshot is working correctly. Now I need to figure out what to do about the offline piece...

Thanks,

Tim

Re: Querying AGOL feature service by date range in ArcGIS Pro

AndrewHargreaves2 — Fri, 08 Feb 2019 15:20:06 GMT

Is it possible to use the #select layer by attribute‌ in #arcgis pro 2.3‌ to select records created today? I'm trying

CreationDate = timestamp NOW()

But getting an error.

Re: Querying AGOL feature service by date range in ArcGIS Pro

JoshuaBixby — Fri, 08 Feb 2019 15:52:19 GMT

It is always helpful to post the entire error message contents.

Re: Querying AGOL feature service by date range in ArcGIS Pro

AndrewHargreaves2 — Fri, 08 Feb 2019 15:59:53 GMT

Re: Querying AGOL feature service by date range in ArcGIS Pro

JoshuaBixby — Fri, 08 Feb 2019 17:21:49 GMT

Using CURRENT_TIMESTAMP instead of NOW(). Whether using NOW() or CURRENT_TIMESTAMP, one needs to be careful since both functions returns date and time down to the seconds or finer, i.e., using an equality sign with NOW() or CURRENT_TIMESTAMP runs a risk of not returning records you might want to select.

I suggest using BETWEEN and putting a range. For example, selecting all records within the 24 hours of now:

CreationDate BETWEEN CURRENT_TIMESTAMP -1 and CURRENT_TIMESTAMP‍

Kory Kramer‌, both the functionality and documentation on querying date/time in web-GIS layers in ArcGIS Pro is a mess. At a minimum, it would be good to update the documentation to reflect the reality of how the software works. For example, reading through SQL reference for query expressions used in ArcGIS—Help | ArcGIS Desktop , it is not clear at all how date/time querying works with web-GIS layers. Is the end-user suppsoed to know what back-end database ArcGIS Server is using for a feature service? CURRENT_TIMESTAMP appears to work most of the time, but is that because it is platform independent or simply dumb luck?

Re: Querying AGOL feature service by date range in ArcGIS Pro

AndrewHargreaves2 — Fri, 08 Feb 2019 21:01:30 GMT

Joshua Bixby‌ Thanks!

How would I modify this to be within the past hour? I used the below:

CreationDate BETWEEN CURRENT_TIMESTAMP - 0.0416666666666667 AND CURRENT_TIMESTAMP

It appears to work but there must be something more elegant...

Re: Querying AGOL feature service by date range in ArcGIS Pro

JoshuaBixby — Fri, 08 Feb 2019 21:21:45 GMT

What about the following, I think it conveys one hour a bit more clearly:

CreationDate BETWEEN CURRENT_TIMESTAMP - 1/24 AND CURRENT_TIMESTAMP‍

Re: Querying AGOL feature service by date range in ArcGIS Pro

AndrewHargreaves2 — Fri, 08 Feb 2019 21:25:03 GMT

sadly that won't work - it runs without error, but fails to select results.

Re: Querying AGOL feature service by date range in ArcGIS Pro

SimonAllard2 — Mon, 04 Nov 2019 03:52:51 GMT

Is this the same bug as this? Select by attribute not working on hosted layer because of the timestamp expression that is automatically populated via clause creation.