Dynamic Date Filter

[#1]

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.

[NorthSouthGIS]

Kudos, this is very helpful.

[RyanNosek]

This is excellent, thank you Jake!

[SvivaManager]

This is great! I just wish it was possible to do in the Filter Widget so that the user could cancel or change it

Shay.

[PhilipReed]

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! 

[JakeSkinner]

aquaraider333‌ where is your data stored?  For example, Oracle Enterprise Geodatabase, ArcGIS Online Hosted Feature Service, ArcGIS Data Store.  Also, what is the query you are trying to write?

[SvivaManager]

I don't think you can have a JSON object inside a value of an object unless it's in an array but I might be wrong (can't test it at the moment to verify). Also, the line with difinitionExpression is missing a comma at the end.

Shay.

[PhilipReed]

Thanks for the prompt reply Sviva and Jake! 

Sviva Manager I'm not sure I understand what you mean. I am curious if there is another area of code that needs to be altered to recognize the definitionExpression parameter. I totally missed the comma, thanks for pointing that out. 

jskinner-esristaff The data being accessed through a hosted ArcGIS Online feature service. The query I'm trying to write scans the GPS_Date field, and returns only those values that have occurred in the last 91 days, to avoid inflation. 

This is my first time taking a dip into JSON, but from everything I've read the single line should be enough to apply the definition query. Thanks for helping me out!

[SvivaManager]

Hi,

I must admit I thought this is a different post (I wrote a post on how to acomplish this in the filter of a web appbuild).

Maybe it doesn't know the variable because of the layer type but that's a wild guess really.

Good luck.

Shay.

[RyanNosek]

Jake Skinner  - 

I was unable to get this to work correctly with ArcGIS Server services/SQL Server database. I have tried various combinations of the query (in case my syntax was off, in case something specific was needed for JSON to parse the query, etc.), but no features are shown in the map. Also, when trying to access the table in the web map I get a "Error: accessing data failed". Below is the defitionExpression saved to the web map's JSON (using AGOL Assistant tool):

"COMPLETEDATE BETWEEN (CURRENT_TIMESTAMP - 60) AND (CURRENT_TIMESTAMP + 1) OR COMPLETEDATE IS NULL"

I ran the same query directly against the SQL database (SQL Server Management Studio) and was able to return results with no problem. e.g:

SELECT *
FROM dbo.ROADWAY_ROADREPORT
WHERE COMPLETEDATE BETWEEN (CURRENT_TIMESTAMP - 60) AND (CURRENT_TIMESTAMP + 1) 

^ returned me correct results

Maybe the recent release ArcGIS Online perhaps broke this functionality?  Is your workflow still working for Hosted Feature Services? Any way you could test against my service and/or other services in SQL Server?

My next attempt is to use the same definition query applied to the feature class/map document before I go to publish to ArcGIS Server. 

However, any guidance to make this work with ArcGIS Online web maps would be great.

Thanks!

[JakeSkinner]

Ryan.Nosek_DuPage‌ can you share the ArcGIS Server service with a Group in AGOL?  You can invite me (jskinner_CountySandbox) to this Group and I can take a look.

[Anonymous User]

I am having no luck with SQL server and ArcGIS server. My attribute table is returned empty. Any idea?I am trying to

all the features collected before 15 days from the last_edit_date field. I am testing with your sample "last_edited_date BETWEEN (CURRENT_TIMESTAMP - 15) and (CURRENT_TIMESTAMP + 1)". but no success. Any idea?

[RyanNosek]

Alex Gole‌

The way your SQL Query is written above should actually be returning "all records last edited within the last 15 days", which might be quite a bit different than what you stated you wanted your query to be: "trying to return features collected before 15 days from the last_edited_date."  Your stated query would actually be a little more complex because you would have to loop through all of your records to find the record with the latest (largest value/most recent) last_edited_date, then compare and only return  those records created before 15 days from the record with the latest last_edited_date. 

Other things to check: make sure you have editor tracking enabled on your feature class (this creates the fields last_edited_date, create_date, created_user, and last_edited_user), and try adding another date field to your dataset to test your SQL queries.  

Jake Skinner‌

Sorry for not getting back earlier - the below query did work for me in the map document as a Definition Query on the feature class before I went to publish an ArcGIS Server Map Service. Still was unable to get it to work in AGO.  I will share a service and add you to a group later today to test in AGO.

COMPLETEDATE BETWEEN (CURRENT_TIMESTAMP - 60) AND (CURRENT_TIMESTAMP + 1)

[Anonymous User]

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:

[RyanNosek]

Alex, 

I had your same issues - it is a valid SQL Query syntax, but not honored using the webmap's JSON work-around Jake showed us. However, the same syntax/query he provided does work if you use it on the feature class in the mxd prior, then publish to your ArcGIS Server.  Hope that helps.

[JakeSkinner]

Alex Gole‌ I was able to get the following query to work to show the features within the last 15 days:

"last_edited_date BETWEEN (CURRENT_TIMESTAMP - 15) and (CURRENT_TIMESTAMP + 1)"

If you want to share your feature service to a Group in AGOL and invite my user account (jskinner_CountySandbox), I can take a look to see if I can get it working.

[Anonymous User]

Jake if you open "Homeless" map. The layer is called "Homeless Camps"

[JakeSkinner]

alex.gole_ELDORADOCOUNTY‌ I'm reproducing the same behavior you are.  What version of SQL Server are you using?  Also, what version of ArcGIS Server?

Can you export the Homeless Camps feature class to a File Geodatabase, zip this File Geodatabase, and upload the zip file to the AGOL Group?  I would like to import this data into my SQL Server instance to see if I am able to reproduce.

[Anonymous User]

Here you go. It is shared. "ziphomeless: is the name of the zip file. I am using ArcGIS Server 10.4.1 and SQL server 2014.

[JakeSkinner]

Alex Gole‌ I was unable to reproduce this when I created an ArcGIS Server feature services using the following definition query:

"last_edited_date > (CURRENT_DATE - 15)"

One difference is that I am unable to store credentials with the service since my ArcGIS Server instance is not externally accessible.  You may want to remove this option and see if you are able to filter the data correctly. Another difference is that I tested with 10.5.1.

[RickBoggs]

Is it possible to do this and update a feature dynamically to show all features where LastEditDate is not within the current Month range? So each month the feature query is updated to show all the features that have not been edited in the current month.

[JackCibor]

Any idea why GETUTCDATE does not work in the place of CURRENT_TIMESTAMP for a service based on  a Geodatabase stored in SQL Server?  Is CURRENT_TIMESTAMP the only DateTime Function supported? 

[EricShreve]

Would any know how I would filter a date field by the last hour? I  am currently rendering a NOAA Observed Weather Station to show current air temperature/wind speed/etc. When I add the map service to AGOL the service creates three points that show the current weather over the past three hours. I would like to show only one point for the past hour. I played around with a couple functions but was unsuccessful in the filter. The code below is what I currently have in JSON format in the Admin Tools. Any help would be appreciated.

"definitionExpression": "(clat BETWEEN 31 AND 37) AND (clon BETWEEN -114.5 AND -108.95) AND (obstime BETWEEN timestamp '2018-02-07 07:00:00' AND timestamp '2018-02-08 06:59:59')"‍

[RickBoggs]

Hey Eric,  I was able to get mine to work with the following code 

 "definitionExpression": "(CURRENT_TIMESTAMP < DATESTART AND CURRENT_TIMESTAMP + 14 > DATESTART) OR (CURRENT_TIMESTAMP BETWEEN DATESTART AND DATECOMP)"

[EricShreve]

Hi Rick. I am currently adding the code like this 

"definitionExpression": (obstime < DATESTART AND obstime + 14 > DATESTART) OR (obstime BETWEEN DATESTART AND DATECOMP)"

 and I am getting an error function in AGOL.

Where does the the obstime field name fit into this, or did you enter the code as above?

[RickBoggs1]

My fields are DATESTART and DATECOMP. CURRENT_TIMESTAMP expression to get the current date time. So if you have observedtime and do not have a date start/completed, you will need to adjust accordingly.

So for yours if you just want to see a station within the last hour you just be able to use the filter in webmap to get obstime 'in last' hour .. you shouldn't have to do that in agol assistant. If for some reason you do here is the sql functions:

Standardized SQL functions in ArcGIS Online—ArcGIS Online Help | ArcGIS 

[Anonymous User]

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

[JakeSkinner]

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:

[Anonymous User]

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.  

[Anonymous User]

I think I got the wrong screen grab on the last comment.  That was another method I was trying out.

[RickBoggs1]

Jason, 

Are you sure the definitionExpression is in the correct location. - may I advise apply a simple filter and then going to arcgis assistant to edit the actual query. At least that way you know it's in the proper location.

[Anonymous User]

Yes, the definitionExpression is in the correct location.  My process was to add a simple filter then edit it.  I did finally find out that the filter I created was filtering the data, only it was returning values 5 hours and 15 minutes rather than 15 minutes.  Our data is given to us through a payload in GMT time using GeoEvent server and we see it as CST time (5 hour difference) in AGOL.  I'm guessing we are viewing CST time yet the JSON filter is filtering GMT time.

[LouiePadilla1]

When you modify the Json for this layer, will that be applied to all the apps that is using the same REST service or is it strictly that one layer inside that specific app/web map?

[JakeSkinner]

Web AppBuilder applications are created from a web map, so what ever web applications leverage the web map you make this change to will reflect the dynamic filter.

[LouiePadilla1]

Thanks Jake! I just wanted to make sure I don't apply the filter to any other apps using a different web map but same service. 

[RickBoggs1]

Anyone have any issues since AGOL was updated? My query above no longer works for adding time to the Current_Timestamp  

"definitionExpression": "(CURRENT_TIMESTAMP < DATESTART AND CURRENT_TIMESTAMP + 14 > DATESTART) OR (CURRENT_TIMESTAMP BETWEEN DATESTART AND DATECOMP)"

..I noticed the policies for allowing non standard queries has been removed.

[RickBoggs1]

So I did a test and used the layer filter to create the query  "definitionExpression": "StartDate BETWEEN CURRENT_TIMESTAMP - 2 AND CURRENT_TIMESTAMP" and that works... but if you change that to a plus it no longer works... even though that is standard syntax.

Any advice? @ Jake Skinner Dynamic Date Filter

[JakeSkinner]

Rick Boggs‌ I was able to reproduce this as well.  Seems something has changed with AGOL.  I'll let you know if I'm able to find a workaround.

[Anonymous User]

Hey Jake,

Have you had any chance to understand what has changed? Is anyone successfully filtering for features greater than CURRENT_TIMESTAMP? I saw Rick Boggs had some troubles with that.

I have this filter working to show features inside a time window:

CURRENT_TIMESTAMP > STARTDATE AND CURRENT_TIMESTAMP < ENDDATE

Another goal is to show upcoming events that are 14 or 30 days from CURRENT_TIMESTAMP.

I'm using hosted feature services in AGOL.

Thanks

[RickBoggs1]

Jake Skinner‌ Any update for us? This setback is becoming quite a nuisance and has rendered a few of our apps useless.

[JakeSkinner]

Rick Boggs‌ & Evan Marshall‌ as a workaround are you able to use the Operations Dashboard?  You can use the Date Selector widget.  Ex:

[RickBoggs1]

No that still wont work.

According to ESRI Documentation - Calculate field values—ArcGIS Online Help | ArcGIS : DATE'<SQL-supported Date Literal>' +/- <Number of Days> = updated date 

Why doesn't this work..?

[AndrewHargreaves2]

Jake Skinner‌ I'm dead in the water here; unable to filter features collected in the past two weeks from a SQL powered ArcGIS Server Service using:

"definitionExpression": "DATE_OPERATED BETWEEN (CURRENT_TIMESTAMP - 15) and (CURRENT_TIMESTAMP + 15)"

However, as others have mentioned above I can successfully query these records directly out of SQL using:

SELECT *
FROM [Corona_Web].[sde].[VALVE_ACTIVITIES_]
WHERE DATE_OPERATED BETWEEN (CURRENT_TIMESTAMP - 15) AND (CURRENT_TIMESTAMP + 15)

[NorthSouthGIS]

try "(DATE_OPERATED BETWEEN convert(date, GETDATE()-15) AND convert(date, GETDATE()+15))"

[NorthSouthGIS]

Jake Skinner‌

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.

[NorthSouthGIS]

Jake Skinner‌

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?

[NorthSouthGIS]

Jake Skinner‌

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:

[JakeSkinner]

NorthSouth GIS‌ I think this may be a bug.  I would recommend logging a tech support incident.  They will confirm, and once the bug is logged, Esri's product team will be notified.

[RickBoggs1]

Jake, 

This is the same issue which I detailed earlier and have reported. The only response I've received is - "It has been logged and will be fixed in an upcoming release of ArcGIS Online." This was Jan 14th. 

When will the next update occur?

[NorthSouthGIS]

Jake Skinner‌

Unfortunately, I have already gone down the route of Esri Tech Support but they could not help me since editing the definition this way "is not technically supported or recommended". If you truly think this is a bug, I would appreciate your help in reporting this to the Product team. 

Thanks!

[MelanieWawryk]

The options seems to have been removed, is there a new way of doing this?