Descripencies between Attribute Table (arcmap) datetime and the value read by arcpy.

KevinMorris — Sun, 12 Dec 2021 16:12:40 GMT

I have recently observed descripencies in time between what is shown in the attribute table in arcmap and what is obtained from reading the date field in python. In particular approximately 1 out of 10 time elements differ by one second. This difference prevents use of the datetime field for identification of duplicates.

Flow: JSON serialized datetime (1) -> python datetime obj (2) -> written to feature class as obj (3) -> read from feature class (type = date) as obj(4).

In the process the time element is consistent from step 1 to 3, however, the read time in (4) may be one second less than observed in the attribute table.

Code to match and/or convert JSON to ESRI 10.0(python) datetime (and others):


def MatchDataType(ESRI_Cursor_Object, JSON_Data_Structure):
    DType = ESRI_Cursor_Object.type
    DName = ESRI_Cursor_Object.name
    JData = JSON_Data_Structure[DName]

    if DType == 'SmallInteger':
        return int(JData)
    if DType == 'Integer':
        return int(JData)
    if DType == 'SHORT':
        return int(JData)
    if DType == 'LONG':
        if long(Jdata) < 2147483647:  # Had to add this due to GeoPro 16 digit Integer range greater than ESRI accomodates with LONG
            return long(JData)
        else:
            msgr('Conversion to long above ESRI limit for long integer')
            return
    if DType == 'Single':
        return int(JData)
    if DType == 'Double':
        return long(JData)
    if DType == 'String':
        return str(JData)
    if DType == 'Date':
        if JData.find('Date('):     # Java JSON date is expressed as milliseconds since IBM inception Date(XXXXXXXXXXXXX-YYYY) X = milliseconds Y=offset (positive or negative?)
                                    # eg. /Date(1348860772000-0400)\  Note that if you do a find on '/Date(' it can fail! Some of my tests were successful others not.
            try:                    # poor man's error checker in case the date stamp is not correct. In this case return nothing
                index = int(JData.find('Date('))
                JData_milliseconds = int(JData[index + 5:index + 5 + 13])       # rip out the milliseconds in the proper placement starting from the location of finding the match
                JData_hourshift = int(JData[index + 5 + 13:index + 5 + 13 + 5])
                dt = datetime.datetime.fromtimestamp(round(JData_milliseconds/1000))  # need to get rid of the tailing bits of real info due to conversion to ESRI date that round this information differently.
                dtn = dt - datetime.timedelta(hours=round(JData_hourshift/100))        # not sure if I should be subtracting/adding this hour shift or what exactly this means. Only want the hour not minutes.                       
                return dtn                                                     # may need to only convert the -04 to integer, need to verify dates against the GeoPro webpage.
            except:
                return
    return

The code writing the datetime element uses a regular insert cursor and converts the JSON data (Gmsg) as a python dictionary list into a matching field in the feature class based upon the feature class field type (predefined).

# Create the ESRI point geometry
                            inPoint = arcpy.Point(SEKI_Location['Longitude'],SEKI_Location['Latitude'])
                            newIncident = rowInserter.newRow()

                            # Populate Shape Attributes
                            newIncident.SHAPE = inPoint
                            # Populate matching fields.
                            for field in fieldList:         # loop through the Feature class attribute names
                                if field.name in Gmsg.keys():
                                    print field.name
                                    newIncident.setValue(field.name, MatchDataType(field, Gmsg))
                                               
                            # add attribute stuff here
                            rowInserter.insertRow(newIncident)
                            # newIncident.setValue(descriptionField, inDescription)

It can then be read lader and matched, however the times may be off by one second.
This is observed as a discripency in the print statement. Approximately one out of 10 times will be off by one second

def IsDuplicateEntry(GeoPro_fc, ComparisonList,FieldList, GeoProMessageObject):
    # SCursor=SearchCursor.reset()      unsupported                                        # reset the search cursor in the feature class for checking the next entry

    Duplicates = 0
    SCursor = arcpy.SearchCursor(GeoPro_fc)
    

    try:
        for row in SCursor:                                                      # Iterate through all the rows to find a match if there is one
            Duplicates = 0
            for ComparisonName in ComparisonList:
                if ComparisonName == "EventDate":
                    print row.getValue(ComparisonName), ' --- ', MatchDataType(GetESRIFieldObject(ComparisonName, FieldList), GeoProMessageObject)                               
                if not(row.getValue(ComparisonName) == MatchDataType(GetESRIFieldObject(ComparisonName, FieldList), GeoProMessageObject)):
                    continue                                                    # if there is any non match we don't need to keep checking
                else:
                    Duplicates = Duplicates + 1
            
            if Duplicates == len(ComparisonList):                                # if all the comparison fields have duplicates then this is a duplicate record and pass a True message
                #del row
                #del SCursor
                return True                                                     # This record is a duplicate - no need to keep searching through the feature class.

        #del row
        #del Scursor
        
        return False
    except:
        #del row
        #del SCursor
        return                                                            # If there is an unusual error just pass the duplicate found flag.

Re: Descripencies between Attribute Table (arcmap) datetime and the value read by arc

JasonScheirer — Thu, 25 Oct 2012 15:25:48 GMT

Could you provide a (greatly) simplified version of your code here without all those extra library calls? You're using a lot of functions and have not provided any context, so I don't know if I can make any assessment of what's going on that can be accurate.

Re: Descripencies between Attribute Table (arcmap) datetime and the value read by arc

JasonScheirer — Thu, 25 Oct 2012 15:27:37 GMT

You may also want to be aware that internally Dates are stored as double precision floats, which are notoriously flaky once you get into large degrees of precision.

Re: Descripencies between Attribute Table (arcmap) datetime and the value read by arc

KevinMorris — Fri, 26 Oct 2012 15:44:30 GMT

I didn't realize the time/date field was of floating point. I'll try some modifications to prevent possible "leakage" and also see if timedelta.resolution will help with similar dates with minor fp mathematic differences.

Re: Descripencies between Attribute Table (arcmap) datetime and the value read by arc

KevinMorris — Wed, 05 Dec 2012 17:00:10 GMT

Because of a number of issues I have converted all code to SQL Server 2012 and use a query layer on a geographic table. Response is excellent and it is easy to code. MapTips does not appear to work, however, even with a spatial index set in SQL and redetermined in ArcGIS 10.1

Issues:

(1) date/time ambiguity
(2) inability to reset the cursor in a feature class (in the documentation but does not work). This requires reopening a feature class multiple times.
(3) no support in arcgis for enterprise size fields such as an 18 digit number (originally converted to char).

Cheers
Kevin

topic Re: Descripencies between Attribute Table (arcmap) datetime and the value read by arc in Python Questions

Descripencies between Attribute Table (arcmap) datetime and the value read by arcpy.

Re: Descripencies between Attribute Table (arcmap) datetime and the value read by arc

Re: Descripencies between Attribute Table (arcmap) datetime and the value read by arc

Re: Descripencies between Attribute Table (arcmap) datetime and the value read by arc

Re: Descripencies between Attribute Table (arcmap) datetime and the value read by arc