Known Issue: UNIX Epoch Milliseconds Date (Datetime) Handling by Esri ArcGIS Feature Service Format

Issue:

ArcGIS Hosted Feature Layers (Feature Services) store datetime values for the Date data type in Coordinated Universal Time (UTC); these UTC values are in milliseconds since the epoch.

The new Esri ArcGIS Feature Service format retrieves date values as-is from the data store, while Esri ArcGIS Online (AGOL), Esri ArcGIS Portal, and Esri ArcGIS Server Feature Services formats automatically convert them into FME Date/Time format.

When writing to a Feature Service with UNIX epoch millisecond values or other values of datetime attribute date type, you may encounter the following error when inserting features with date values in UNIX Epoch milliseconds:

ArcGISFeatureServiceWriter: append error: Write job failure: 'Cannot convert data for field '<Attribute Name>'.'

ArcGISFeatureServiceWriter: append error: Write job failure: 'Cannot convert data for field '<Attribute Name>', type 'esriFieldTypeDate'.'

ArcGISFeatureServiceWriter: INSERT error for a feature in <Feature Service Layer Name>. The error code from the server was '10500' and the message was 'Cannot convert a value of type 'String' to 'TIMESTAMP'.'

ArcGISFeatureServiceWriter: INSERT error for a feature in <Feature Service Layer Name>. The error code from the server was '1000' and the message was 'Conversion failed when converting date and/or time from character string.'

And the following error when updating features with date values in UNIX Epoch milliseconds:

ArcGISFeatureServiceWriter: UPDATE error for a feature in <Feature Service Layer Name>. The error code from the server was '1000' and the message was 'Conversion failed when converting date and/or time from character string.'

ArcGISFeatureServiceWriter: UPDATE error for a feature in <Feature Service Layer Name>. The error code from the server was '10500' and the message was 'Cannot convert a value of type 'String' to 'TIMESTAMP'.'

Cause

Datetime values (Date data type) in hosted feature layers (Feature Services) are stored in Coordinated Universal Time (UTC) as milliseconds since epoch for UTC, also referred to as UNIX Epoch Milliseconds. The storage location for these UTC datetime values depends on the platform: ArcGIS Enterprise/Portal stores them in the database and ArcGIS Online stores them in the ArcGIS Online Feature data store.

Legacy Feature Service formats, as documented in our Esri ArcGIS Online (AGOL) Feature Service User Attributes, Esri ArcGIS Portal Feature Service User Attributes, and Esri ArcGIS Server Feature Service User Attributes documentation, FME converts Esri Date data types to the FME datetime format automatically. With the Esri ArcGIS Feature Service format (accessible through Esri ArcGIS Connector FME Hub Package), as implied in the User Attribute documentation, Esri Date data type values are retrieved as-is and is not converted into FME datetime format automatically. This means that datetime values will be retrieved as milliseconds since epoch for UTC and will have a length of 8. You will see the same date value and length when querying date fields for a feature layer using the ArcGIS REST services. 

Example of querying for "created_date" date field in a Hosted Feature Layer on AGOL using Esri ArcGIS REST Services:

The ArcGIS Feature Service format is not currently lenient when writing the data, and additional configuration may be required. See the Workaround section below. 

Workaround

Please update the Esri ArcGIS Connector on FME Hub. 

Attribute's data type enforcement occurs at the Writer/FeatureWriter or through specific enforcement transformers such as AttributeValidator or Tester. Because of this behavior, configuring the AttributeManager by specifying a data type for an attribute alone will not resolve data type conversion issues.

If the incoming value fails to match the expected datetime format, the Writer/FeatureWriter will either:

• Complete the translation successfully, but write no data (date values) to the Feature Service
• Trigger a data type conversion error

To properly handle datetime data types and ensure that the date values are written, you must use a combination of two configurations:

1. Configure the Writer's User Attribute definition to specify 'date' as the data type
2. Apply the DateTimeConverter transformer to standardize the incoming date format into the accepted format

Currently, DateTimeConverter does not support UNIX Epoch milliseconds for input or output but does support UNIX Epoch seconds. Conversions involving UNIX Epoch milliseconds will require using the AttributeManager's Arithmetic Editor. 

For UNIX Epoch milliseconds to seconds, use @Value(ATTRIBUTENAME)/1000. 

For UNIX Epoch seconds to milliseconds, use @Value(ATTRIBUTENAME)*1000.

There are two general workaround approaches: one that does not use the Arithmetic Editor for the Writer/FeatureWriter's User Attribute value specification configuration, and another that does. Both approaches use User Attribute Definition to specify 'date' as the data type.

Without Using the Arithmetic Editor for User Attributes on the Writer/FeatureWriter

For ArcGIS Online Feature Service

The following datetime formats should write successfully to attributes with a date data type:

• FME datetime
• FME datetime with Offset
• ISO datetime
• ISO datetime with Offset

For ArcGIS Portal Feature Service

The following datetime formats should write successfully to attributes with a date data type:

• FME datetime
• FME datetime with Offset

Using the Arithmetic Editor for User Attributes on the Writer/FeatureWriter

For ArcGIS Online Feature Service and ArcGIS Portal Feature Service

The following datetime formats should write successfully to attributes with a date data type:

• UNIX Epoch Milliseconds
• FME datetime