Querying AGOL feature service by date range in ArcGIS Pro

1987
15
10-01-2018 08:56 AM
deleted-user-ka73FBRGJUOO
New Contributor

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.

15 Replies
AndrewHargreaves2
Occasional Contributor III

0 Kudos
JoshuaBixby
MVP Esteemed Contributor

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?

AndrewHargreaves2
Occasional Contributor III

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

0 Kudos
JoshuaBixby
MVP Esteemed Contributor

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

CreationDate BETWEEN CURRENT_TIMESTAMP - 1/24 AND CURRENT_TIMESTAMP
AndrewHargreaves2
Occasional Contributor III

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

0 Kudos
SimonAllard2
New Contributor III

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. 

0 Kudos