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.

[DanielLugo_Mercado]

I've been having  an issue regarding this topic.

I have this layer in a web map with point features that represent a type of incident. Each incident has a date of occurrence (a column with a date format named fecha) associated to it with no information on time (dd/mm/yyyy). I want the web map to filter the layer so that it only shows incidents that occurred this year by editing its JSON file (using AGOL Assistant) and inserting a dynamic date SQL Query.

Here is what I’ve been trying to use:

                (EXTRACT(YEAR FROM fecha) =  EXTRACT(YEAR FROM CURRENT_DATE))

The query filters the layer by equating the year from the DATE column (fecha) to the year corresponding to the current date.

The query works, but it returns all the incidents that occurred on the current year (2019) except for December 31 of that year. However, it also includes the incidents occurring on December 31 of the previous year (2018). I don’t know if someone has already experienced this and if there’s a workaround.  

[JakeSkinner]

Daniel Lugo Mercado‌ you may want to try doing this with Arcade.  See the following document:

https://community.esri.com/docs/DOC-13792-dynamic-date-filter-with-arcade 

I've found this easier to set up, and a little more stable.

[DanielLugo_Mercado]

Hi Jake,

Thank you for your reply! We already tried that route but didn't find a way to implement the filter we wanted through Arcade. Arcade successfully shows the features we want to see, but doesn't filter them in the attribute table. This means that we can still select features from other years on the map even though they're not visible.We used the following Arcade code:

var dateField = "fecha" //Date Column

var CurrentYear = Year(Now())
//The following statement 'selects' the features that occured between 2018 and 2020 (ie. 2019)
if(Year($feature[dateField]) > CurrentYear-1 && Year($feature[dateField]) < CurrentYear+1){
    return '2019'
}

Is there a way we could also dynamically filter features from the attribute table using Arcade?

[JakeSkinner]

Daniel Lugo Mercado‌ if this is an ArcGIS Online service, can you share it with an ArcGIS Online Group and invite my account (jskinner_CountySandbox)?  I can take a look to see how to configure the query.

[GeeFernando]

Great post Jake Skinner‌,

If folks aren't allowed to  uncheck -  "allow only standard SQL queries", you could still use "in the last" and "not in the last" dynamic date expressions on webmap filters or when defining feature layer views.

The following expression will show features edited in the last day. Hope this helps.

Gee

[Jay_Gregory]

Has anyone figured out, using a feature layer in a SQL database, how to remove the time portion of the timestamp.  

So if I only want to filter by events that happened yesterday, BETWEEN (CURRENT_TIMESTAMP-1) and (CURRENT_TIMESTAMP + 1) doesn't really work.  

I need something like BETWEEN DayBegin(CURRENT_TIMESTAMP-1) and DayEnd(CURRENT_TIMESTAMP - 1);  

Is there an equivalent query I could set in AGOL Assistant to achieve this?

[PaulHoefflerGISS]

Jake Skinner‌, it appears that what's shown in the original content won't be possible the vast majority of ArcGIS Online organizational accounts, unless what you've shown is more recently considered within the scope of standard SQL queries? This has been mentioned previously by Melanie Wawryk and Gee Fernando. 

We can no longer disable standard queries, although we wouldn't have done that as a security precaution if we could.

I'm currently in the process of logging an enhancement request with Esri Technical Support to be able to support this natively in AGOL, unless that's currently possible and we're not aware. If this work-around is no longer possible perhaps the document can be updated so user don't have to wade through 50+ comments before coming to that realization. Thanks for any info on this.

[JakeSkinner]

This should be possible in AGOL now:

[AndresCastillo]

wow.

excellent.

[CANC-BrianBowersAGOL]

@JakeSkinner, I bet the reason that your filter expression was returning incidents occurring on December 31 of the previous year is because your dates are in UTC; not converted to your local time zone. So, for example, here in California, I would have to use something like "definitionExpression": "last_edited_date > timestamp '2021-01-01 07:59:59'" (because Pacific Standard Time is 8 hours after the Prime Meridian). A more dynamic filter to restrict records to the just current year would be:

"definitionExpression": "(EXTRACT(YEAR FROM last_edited_date) > EXTRACT(YEAR FROM CURRENT_DATE)) OR ((EXTRACT(YEAR FROM last_edited_date) = EXTRACT(YEAR FROM CURRENT_DATE)) AND (EXTRACT(MONTH FROM last_edited_date) >= 1) AND (EXTRACT(DAY FROM last_edited_date) > 1) OR (EXTRACT(HOUR FROM last_edited_date) >= 8))"

Was your expression returning incidents from the whole entire day of 12/31, or just the few hours worth that equate to your timezone offset?

[kmsmikrud]

Hello,

I am also looking to have a dynamic date filter in the web map  and similar to @CANC-BrianBowersAGOL I want only features to show on the map for the current date (AK Time) so just 1 hour different than the example posted using Pacific Standard Time. 

It is my understanding if I use the recommendation above from @JakeSkinner  "CreationDate" in the last 1 day, this really mean last 24 hours and then spans 2 days at times depending on the hour which isn't what I want. I do see the option for "CreationDate" is after and then you can choose a date. If I set a filter for "CreationDate" is after 2/26/21 and I want to show features collected only on 2/27/21 in AK Time, does the 2/26/21 refer to local time or UTC or I do translate this? Although this filter is no longer dynamic then either. 

I also did try the AGOL Assistant and DefinitionQuery, but I'm not sure if this is still the best way since the setting on SQL queries is no longer showing. I also tried the arcade expression but besides features still showing in pop-ups I lose the symbology I want displayed in the map.

What are the recommended solutions? I feel like this should be a straightforward request to only show features from current 'local time zone' day. 

Thanks in advance!!!

[LindseyStone]

I'm attempting this using my hosted portal.  I was able to get it to work correctly on my web map, but when I go to Collector nothing shows up.  Has anyone else experienced this when using this method?

I have guys doing inspection reports within Collector and they won't to only show the points that haven't been inspected in the last 4 months.

[jschuckert]

@LindseyStone I am experiencing something similar. I can go in and modify the map's JSON, however that doesn't appear to be supported in my case in Field Maps--the end product for the user. My data source is coming from ArcGIS Server not AGOL hosted, so unfortunately those queries aren't built into the UI, hence the backend JSON workaround. I am using the new Map Viewer and Field Maps.

@JakeSkinner can you confirm non-standard queries such as this are supported outside the web map in say Collector or Field Maps?

I require two non-standard queries in two different maps but both pointing back to the same map service for the source. I was hoping this would work as a way to avoid publishing two different map services with the dynamic date set back in the map document at the time of publishing.

Jared

[jschuckert]

A quick update, after trying some other date-related SQL queries this one appears to work in both the web map and in my case Field Maps:

Original query, only works in web map (added via backend JSON):

EDIT_DATE BETWEEN (CURRENT_TIMESTAMP - 14) and (CURRENT_TIMESTAMP + 1)

Modified query, same date range, works in both web map and Field Maps (added via backend JSON):

EDIT_DATE >= CURRENT_TIMESTAMP - 14

Hope this helps if you have not yet already found a solution @LindseyStone .

--Jared--

[LindseyStone]

@jschuckert 

I have successfully, been able to implement symbology change based off date stamps utilizing custom arcade expressions in the Style section of the Web Map.  Here is an example of one.  This works in Field Maps, Web app Builder, and Dashboard seamlessly.  The only downfall is if you modify the expression, then you have to reset up all your symbology again.

var tdate = Now();
var insp = Date($feature.lastmaint);
var pastdue = DateAdd(tdate, -110, 'days');
var duesoon = DateAdd(tdate, -60, 'days');
if (insp < pastdue)
{ return 'Due'}
else if (insp < duesoon)
{ return 'Due Soon'}
else
{ return 'Completed'}