Making a Date-based filter condition similar to "in the last" for feature services

[nacelle_pylon]

I am working on a date based filter condition for a sql based feature service. I need to automatically show on a webmap hydrants flushed for the last four days. Unfortunately because it is not a hosted feature service I do not have the "in the last" option when I select date range. Per ESRI documents:

https://enterprise.arcgis.com/en/portal/latest/use/apply-filters.htm

"The in the last and not in the last operators are only available for hosted feature layers, not for hosted spatiotemporal feature layers or ArcGIS Server feature services."

Is there anybody that has figured out a process to show this information? Whether through some slick workaround, coding or anything? I really need this process automated and would prefer not to manually update.

[TanuHoque]

@nacelle_pylon 

Here is a blog that was specific to map services. But you might be able to take advantage of this too specially if you publish with that def query set with the layer prior to publishing.

[UPDATED: missed pasting the url in the first go]

https://www.esri.com/arcgis-blog/products/arcgis-enterprise/mapping/how-to-author-a-feature-layer-to...

[nacelle_pylon]

I'm sorry I do not see a link to the blog provided. Can you send the link? thanks

[nacelle_pylon]

@TanuHoque Where is the link to the blog?

[TanuHoque · Accepted solution]

@nacelle_pylon 

doh!!!

here you go: https://www.esri.com/arcgis-blog/products/arcgis-enterprise/mapping/how-to-author-a-feature-layer-to...

[nacelle_pylon]

Wow thanks! this might be it. Going to test and if so will accept as solution.

[jcarlson]

What is the structure of the data? Is the "flush date" in the main table, or in some related table?

If you're using an SQL-based service, can you publish out a Query Layer? That would allow you to use your native SQL methods prior to publishing. What DB system are you using? MSSQL, Postgres, etc.

[nacelle_pylon]

@jcarlson Flush date is in the main table. DB system we are using is MS SQL. 

Any chance somebody has a good sql expression to show flushing in last four days?