Querying AGOL feature service by date range in ArcGIS Pro

[deleted-user-ka73FBRGJUOO]

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.

[AndrewHargreaves2]

[JoshuaBixby]

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]

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

[JoshuaBixby]

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

CreationDate BETWEEN CURRENT_TIMESTAMP - 1/24 AND CURRENT_TIMESTAMP‍

[AndrewHargreaves2]

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

[SimonAllard2]

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.