2

I have some data in a file geodatabase feature class including some date fields. The precision on the date fields is to milliseconds and this is important.

When I copythe feature class from the fGDB to an SDE enterprise geodatabase (MS SQL Server 2014), the date precision appears to have some odd rounding applied to it.

Here's what the difference looks like:

>>> with arcpy.da.SearchCursor("Form_1_fGDB", ['EditDate']) as rows:
    for row in rows:
        print row[0]
2018-12-03 21:22:57.565000
2018-12-03 21:24:59.411000
2018-12-03 21:29:24.494000
2018-12-03 21:32:23.298001
2018-12-03 21:36:04.656000
2018-12-03 21:42:11.316000
2018-12-03 21:47:45.409001
2018-12-06 23:40:23.387000
2018-12-06 22:54:35.083000
>>> with arcpy.da.SearchCursor("Form_1_SDE", ['EditDate']) as rows:
    for row in rows:
        print row[0]
2018-12-03 21:22:58.000001
2018-12-03 21:24:59.000001
2018-12-03 21:29:24
2018-12-03 21:32:23
2018-12-03 21:36:05
2018-12-03 21:42:11
2018-12-03 21:47:45
2018-12-06 23:40:23
2018-12-06 22:54:35.000001
>>>

It looks like they are either rounded DOWN to the nearest whole second, or rounded UP to the whole second plus 1 millisecond.

I get the same result whether using:

  • right-click and copy/paste in the catalog pane
  • arcpy.FeatureClassToFeatureClass_management()
  • arcpy.Append_management()

Why is this happening, and how can I preserve date value precision when copying data from an fGDB into an SDE GDB?

Improve this question
6
  • Perhaps this will illuminate stackoverflow.com/questions/36414315/… . Although you're using GIS software you have to work within the constraints of the database data types. Note: I have no idea what will happen to your SDE data if you change the field type as described, it may work fine or cause the field/featureclass to be unrecognizable it is impossible to tell until you actually try - then please answer your own question with this valuable information for future users.
    – Michael Stimson
    Commented Dec 7, 2018 at 3:18
  • I've looked in SQL Server, and ArcGIS has definitely created the field as a 'datetime2' field type. It is NOT a 'datetime' field type. So it should me more than capable of this precision.
    – Son of a Beach
    Commented Dec 7, 2018 at 3:24
  • 1
    By coincidence, while I was writing that last comment, I got a phone call from ESRI (Australia) support (I'd logged an issue with them yesterday). They have been able to replicate the problem and they think it may be an ArcGIS bug.
    – Son of a Beach
    Commented Dec 7, 2018 at 3:30
  • 1
    Enterprise geodatabases bind datetime data types through struct tm, which stores seconds using an integer. The decimal values you're seeing are due to floating point representation issues.
    – Vince
    Commented Dec 7, 2018 at 4:53
  • 1
    Thanks Vince. So if I understand you correctly, you're saying that ArcGIS SDE geodatabases cannot store a datetime with any more precision that whole seconds? (Even when the underlying RDBMS datatype can.) Can you direct me to any documentation for the ArcGIS side of this?
    – Son of a Beach
    Commented Dec 7, 2018 at 4:55

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.