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)