Are dynamic date queries supported for spatiotemporal big data store layers?

[AndrewRudin1]

I have some AVL data hosted in a 10.6.1 spatiotemporal big data store.  I'm trying to apply some filters in Pro based on the GPS timestamp but it's not working.  The Pro documentation covers supported functions for the traditional RDMS, shapefile, and file database formats, but doesn't discuss compatibility for the newer ArcGIS Online, Relational Data Store, and Spatiotemporal Data Store options.

Specifically I'm trying to do two queries on a single spatiotemporal hosted feature layer and contains historical vehicle location data.  The point layer has a VEHICLE_ID column to store the unique ID for each vehicle, and a GPSTIME field for the time the location was caption.

Query #1:  Filter the dataset to only records with a timestamp in the last 24 hours.  I tried a def. query GPSTIME > (CURRENT_TIMESTAMP() - 1), but that returned an expression error.

Query #2: Filter the dataset to only the record with the latest timestamp for each vehicle_id.  I believe with a traditional RDMS, this type of query can be handled with a whereclause along the lines of the following:  

gpstime = (SELECT max(gpstime) from MYGPSTABLE WHERE vehicle_id = MYGPSTABLE.vehicle_id)

[AndrewRudin1]

Update:  

For the basic dyamic date filter (Query #1), I did find some detail in the REST API help.  Although I'm using Pro instead of the REST API, they do have some notes in the Feature Service/Layer Query method  on the WHERE parameter:  Query (Feature Service/Layer)—ArcGIS REST API: Services Directory | ArcGIS for Developers .

However, It still didn't work.  I entered the following query in Pro, and although the query validates, the layer still shows records older than 1 day.

gpstime >= ( current_timestamp - interval '1' day)

[AdamMiller4]

Andrew,

Did you ever get something similar to this to work?  I'm running into the same issue querying AVL data on our BDS.

[MatejVrtich]

Hi Andrew,

This expression is working for me (use SQL Mode in Pro's Definition Expression settings):

gpstime >= 'now-24h'

The Spatiotemporal BDS is built on top of the Elasticsearch technology where I found this: Range query | Elasticsearch Reference [7.4] | Elastic .

Hope it helps,

Matej