Timestamp query returning incorrect features

07-12-2021 07:54 AM
New Contributor III

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.EditDate.JPG

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)


    ## 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

    ## otherwise there have been no updates in the last hour
        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.


~ Mapping my way to retirement
0 Kudos
3 Replies
New Contributor III

Going to guess from your spelling you're in the UK. If so, at this moment the time is as below:

4:51 PM Monday, in Coordinated Universal Time is

5:51 PM Monday, London, UK (GMT+1)
So, it would appear that your queries are being executed in UTC and not your local time zone - at least, this might explain the discrepancy. I run into this frequently with servers in various time zones - the queries go by the system time so there is some offset required at times. You mentioned running the above in a Notebook, but did not say whether that is a local notebook or Notebook Server/ ArcGIS Notebooks. I surmise if you are using Notebook Server/ArcGIS Notebooks the problem may be that the server is configured to use UTC.
So, long story short, you probably just have to account for the offset. Hope this helps!
0 Kudos
New Contributor III

Thanks @emedina, I thought similar myself. It's currently being run from my local machine with a plan to setup as a Notebook on ArcGIS Online for a scheduled run. I find it odd that the display time in AGOL for the EditedDate field is correct, but accessing the date stored in the field programmatically puts it an hour behind. 

You were close I'm in Ireland :) 

~ Mapping my way to retirement
0 Kudos
New Contributor III

Ah, in that case Mayo for Sam :smiling_face_with_smiling_eyes:.

I think I may have gotten my wires crossed earlier. I believe the problem is programmatically you have to explicitly supply the offset whereas in the browser the conversion is done for you under the hood. Yes, as you noted in the browser the date appears right but the value is stored in UTC.  Anyhow, this related post should get you going:

0 Kudos