Timestamp query returning incorrect features
Setting the scene...I have a feature service with one layer and one table, the table is related to the layer. I want to transfer updates from a single field within the last hour from the related table to the layer. Basically updating a status from the related table to the status field in the layer. While the script below runs fine, and the query looks good, it actually picks up edits that are occurring within the current hour rather than honouring the query. If you look below there are four records in the table, three of which are edited after 2pm.
Now let's look at the script...
from arcgis import GIS
from datetime import datetime, timedelta
## get now ad a datetime
now = datetime.now()
## end hour eg. 2021-07-12 14:00:00
date_end_hour = datetime(now.year, now.month, now.day, now.hour)
## start hour eg 2021-07-12 13:00:00
date_start_hour = date_end_hour - timedelta(hours=1)
conn = GIS("home")
## Feature Service ID Dict
fs_id_dict = {"area_name1" :"fs id",
"area_name2" : "fs id"}
## the common field in from tbl to update in lyr
fld = "appstatus"
## query used to return records from tbl that fall within the time range
edit_query = "EditedDate >= timestamp '{0} {1}:00:00' And EditedDate < timestamp '{2} {3}:00:00'".format(date_start_hour.date(), date_start_hour.hour, date_end_hour.date(), date_end_hour.hour)
## for area, fs in fs_id_dict
for name, fs_id in fs_id_dict.items():
## access the fs
item = conn.content.get(fs_id)
## reference the layer and table
lyr = item.layers[0]
tbl = item.tables[0]
## dictionary to hold the parentguid : appstatus
tbl_dict = {}
## interested in all records that fall within the time range
tbl_query = tbl.query(where=edit_query)
print(edit_query)
## if more than one record found
if len(tbl_query.features) > 0:
print(name, len(tbl_query.features))
## add entry to tbl_dict for all records
for f in tbl_query.features:
tbl_dict[f.attributes["parentguid"]] = f.attributes[fld]
## look at all the features in the layer
lyr_query = lyr.query()
## if there is a matching parentguid in the tbl the update the
## appstatus in the layer
for f in lyr_query.features:
if f.attributes["GlobalID"] in tbl_dict:
f.attributes[fld] = tbl_dict[f.attributes["GlobalID"]]
##uncomment line below to update the lyr
#lyr.edit_features(updates=query.features)
## otherwise there have been no updates in the last hour
else:
print(name, "No Features")
Now look at the output for print statement for the edit_query and the name, num of features...it says that for Area1, which is the EditedDate table above, there are three that fall within 1pm and 2pm, which is incorrect, they fall between 2-3pm, so the output should be "No Feartures"
EditedDate >= timestamp '2021-07-12 13:00:00' And EditedDate < timestamp '2021-07-12 14:00:00'
Area1 3
Anyone come across this before? or offer a better alternative to querying the correct records using the EditedDate field?
This will be saved as a Notebook and scheduled to run every hour across a multiple feature services.
