Arcade - Filter Feature Set by Date

Question

I am trying to filter a feature set using a date field on ArcGIS Online. I need the filter to return a feature set of all records after a particular day. However, when I run the filter, I simply get back my starting feature set. In other words, it runs but it doesn't seem to be recognizing the filter expression that I've entered.

In the code below I'm creating two feature sets, one on line 4 and the other on line 5. Line 4 produces the feature set that I'd expect, but it's the filter on line 5 where I'm trying to get all records that have a date after the inspection date that doesn't seem to be working.

Is there additional formatting that I need to do to my inspectionDate variable to get it to evaluate correctly against my DATE field?

var inspectionDate = $feature.DATE
var client = $feature.CLIENT
var meter = $feature["SERIAL__"]
var fSetByMeter = OrderBy(Filter($layer, 'CLIENT = @client AND SERIAL__ = @meter'), 'DATE ASC');
var fSetCurrentDate = OrderBy(Filter(fSetByMeter, 'DATE > @inspectionDate'), 'DATE ASC')
return fSetCurrentDate

TórshavnarKommuna · Answer

Hi.

I was having the same problem. Assuming the the data are coming from a ArcGIS feature layer, when you filter a featureset, it is sending a request to REST API to a Query operation. After testing and checking some documentation, I found out that to define a "where clause" for query operation using a date field it is necessary to add DATE in front of date value. So In your case this could work:

var fSetCurrentDate = OrderBy(Filter(fSetByMeter,'DATE > DATE @inspectionDate'), 'DATE ASC')

However, I don't know if using the DATE as a field name can have some issues of its own, as it is often a reserved keyword in DMBS system. Also, I didn't got it to work by passing date in variable such as "@insperction_date". But here is an example with my code which was working for me:

var read_out_date = Text($feature.collection_date, "Y-MM-DD");

// Define an sql query and filter the data
var sql = "collection_date < DATE '"+ read_out_date +"'";

var filteredDataSet = Filter(inputFeatureSet, sql);

Finally here is a link to a post about querying date fields at REST API:
https://www.esri.com/arcgis-blog/products/api-rest/data-management/querying-feature-services-date-time-queries/

pmccord · Answer

Great suggestion Josh. I converted my feature set to a dictionary, and while populating the date value of my dictionary, I converted the date to Unix time. I converted to Unix time as a result of stumbling on this post:
https://community.esri.com/t5/developers-questions/arcade-dictionary-to-featureset/td-p/1047117

Once I had all the date values in Unix time, I converted my dictionary back to a feature set. I then converted the inspectionDate value to a Unix timestamp as I did above when creating the dictionary:

var inspectionDate = $feature.DATE
var start = ToLocal(Date(1970, 0, 01, 0, 0, 0, 0))
var dt = DateDiff(inspectionDate, start, 'milliseconds')

After doing this I was able to successfully filter for all dates after the inspection date.

I'm sure there's a more efficient way to do this, but I'm calling it good now that I'm getting the result I want. Thanks again!

jcarlson · Answer

Hard to say for sure, but I would guess that it's because DATE is a reserved SQL keyword, which can throw off a query, as the database may be expecting something else when it sees that keyword, rather than assume it to be a field in your table.

What Arcade profile are you using, and where is the featureset coming from? Is it feasible to construct a featureset manually by populating a dict of features and rename the date field there?