Dynamic Date Filter
Users have the need to apply a dynamic date filter to a feature layer within their web map . For example, they may need to show all features that were edited on the current day. Instead of having to manually update the filter in the web map each day, the below steps will walk you through how to have this date update automatically each day.
Note: Before proceeding, you will want to make sure 'Allow only standard SQL queries' is unchecked within My Organization > Edit Settings > Security tab:
Steps:
1. Within your web map, apply a filter to your layer:
2. Save your web map and use a utility such as GeoJobe Admin Tools or AGOL Assistant to edit the JSON. In the below example, the AGOL Assistant is used. After signing into the AGOL Assistant using your AGOL credentials, click 'I want to' and choose 'View an Item's JSON':
3. Navigate to and select the web map you saved previously. On the right scroll until you see Data and find the layer that has the filter (definitionExpression) applied:
4. Click the pencil at the top right to begin editing. You will then want to update the definitionExpression using a SQL function (i.e. GETDATE()). In the below example, by subtracting 1 and adding 1 to the GETDATE function will subtract/add one day. Also, the CONVERT function is used to truncate the time from the date. This is to ensure you are getting the entire day rather than a 24 hour period. So, the expression will filter all features between yesterday and tomorrow (i.e today).
NOTE: The definition expression will be relevant to the geodatabase you are using. The above will work for a hosted ArcGIS Online feature service. However, if you are using a hosted service in the ArcGIS Datastore (which stores the data in a PostgreSQL database) or a feature service consuming data from Oracle, the definition expression would be:
"last_edited_date BETWEEN (CURRENT_DATE - 1) and (CURRENT_DATE + 1)"
For SQL Server, the definition expression would be:
"last_edited_date BETWEEN (CURRENT_TIMESTAMP - 1) and (CURRENT_TIMESTAMP + 1)"
5. Save your changes by clicking the save option at the top right.
6. Go back to ArcGIS Online and re-open your web map by going back to 'My Content' and selecting to open the web map in the ArcGIS Online Viewer. If you click on the Filter option, the expression should be blank under both the View and Edit tabs:
The filter will be dynamic, so as each day passes the filter updates with no user interaction.
Thanks for this post, this is exactly what I was looking for!
I'm currently struggling to get the "definitionExpression" to be read properly, and consistently have a non-descriptive syntax error returned that prevents saving. Has anyone else experienced this issue?
Thanks!
Ryan,
"all records last edited within the last 15 days" is what I want. Sorry if I wrote something wrong.
What I get from that query is:
Anyone have luck filtering down to the past x hours/minutes rather than just days? I have edited the JSON with the following equation:
"definitionExpression": "(GPSFixTime BETWEEN (current_timestamp -0.01041667) AND (current_timestamp +1)"
My intention here is to view the past 15 minutes of data. Interesting thing is when I open up the filter in AGOL, it says it is filtering the last 15 minutes, yet the data being displayed is beyond 15 minutes.
I tried another filter that showed the past 1 hour (-0.04166667) and got the exact same number of records as filtering the past 15 minutes. The filter window said "GPSFixTime in the last hour".
Jason Ehrig-Page it looks like you have an extra parenthesis in your query:
"definitionExpression": "(GPSFixTime BETWEEN (current_timestamp -0.01041667) AND (current_timestamp +1)"
Does the same occur when you have the following:
"definitionExpression": "GPSFixTime BETWEEN (current_timestamp -0.01041667) AND (current_timestamp +1)"
I tested this and it worked successfully for me:
I have tested without the extra parenthesis with the same results as well with a number of other similar methods. I was trying to filter other parameters, thus the reason for the extra parenthesis. If you are able to get the filter to work, then it tells me that at least AGOL does support this and this is possibly something with our settings or data.
I think I got the wrong screen grab on the last comment. That was another method I was trying out.
Rick Boggs & Evan Marshall as a workaround are you able to use the Operations Dashboard? You can use the Date Selector widget. Ex:
I want to apply this same logic to a hosted view. However, I found an issue:
If I add the hosted view (with a filter on it set in the Visualization tab of the hosted view item details)
by "Add layer from web" in a new web map
Then the filter is NOT honored (same for labels and I'm unsure what else.. but that is less relevant to my overall point).
This leads me to believe there is a bug when adding a hosted view from the rest end point.
If I apply the filter by augmenting the view definition:
Then the filter is honored. HOWEVER, I cannot see this "view definition" in the JSON, therefore I cannot apply a dynamic date filter.
Any thoughts on a workaround for applying a dynamic date filter to a hosted view and honoring through the REST end point? How can I edit the JSON of the hosted view definition? OR can Esri please fix this bug.
I found the definition query when trying to update the layer definition on the Admin REST interface, but when I change the definitionQuery or ViewDefinitionQuery to use that "GETDATE" function you mention above, I get an error that says invalid System.String and invalid 'DefinitionQuery'.
I noticed that there is a "DefinitionQuery' and a 'ViewDefinitionQuery' with the exact same values in my layer definition. Why are there two? What is the difference? Any documentation?
PS. I also went ahead and set my filter in AGOL to "In the last 600 days" to see how AGOL populates the "Current Time" in the view definition. They used "CURRENT_TIMESTAMP"
and so I changed the query to show in the next 600 days but I am still getting the same error message:
The options seems to have been removed, is there a new way of doing this?
