I am investigating an issue on a table in the database_name registered geo-database (archiving enabled) used as the datastore for a registered feature service and am hoping that the community might be able to help or have any ideas. The table name is [database_name].[sde].[feature_class].
The issue we’re running into is that the created_date and last_edited_date fields are storing date values that are 4 hours in the future, beyond UTC time, which is already 4 hours in the future based on eastern time. They’re not storing UTC values, which is what we’d expect. What is interesting is that the GDB_FROM_DATE and GDB_TO_DATE fields are recording the correct UTC values and since these values are ESRI constructs, I'd expect those to calculate/pull time references from the same location on the same machine. Additionally, fetching UTC time at the database level also yields the correct time which lends itself to the theory that this specific to Editor Tracking fields.
See screenshot below. For the highlighted record, I created/edited it around 12:10 Eastern, which is 16:10 in UTC. The GDB_FROM/TO dates correctly show 16:10. But then the created_date and last_edited_date fields show an additional 4 hours added on:
Some additional notes on testing.
1. In an edit session in ArcMap on ProClient01, manually adding points in a map session through a direct database connection in ArcMap yielded incorrect time in database record (no ArcGIS Server interaction). Editor Tracking is improperly adding an additional 4 hours from local time to UTC conversion (+8 for eastern).
2. For reference, GDB_TO and GDB_FROM date times are correct (ESRI Archiving fields), which helps support this is specific to the Editor Tracking fields.
3. When editor tracking is disabled and re-enabled through the “Enable Editor Tracking” geoprocessing task, if database time is specified (I believe this was in the “record_dates_in” optional choice), we get the expected time stored in UTC in the record. This yields the expected time but is different workflow than any other environment we have worked in to my knowledge. In other ArcGIS Server/Portal environments, we've never had to specify database time. We want time to be recorded in UTC since data is collected across many time zones.
We’re wondering if something like this is happening: the registered geo-database is storing everything in UTC by default, but somewhere along the way the Esri Editor Tracking functionality thinks the database is in Eastern Time zone, so it’s adding an additional 4 hours onto the time being saved to the database. We still don’t really know when in the workflow the additional 4 hours is getting applied, but we were able to rule out ArcGIS Server due to (1) above.