Tag: Postgresql

ArcGIS Data Store “data/time field value out of range” error and solution

The problem

Recently I encountered a problem while testing Portal for ArcGIS feature layer editing. I was able to add new features, update attributes and vertices but deleting features didn’t work for me. No error message after the delete button is clicked.

I have an ArcGIS Server site set as the hosting server for the Portal for ArcGIS site. ArcGIS Data Store is installed and set as the ArcGIS Server site’s managed database. Portal for ArcGIS, ArcGIS Server and ArcGIS Data Store are all 10.4.1.  The standard setting recommended by ESRI was used to set the system.

The Investigation

I tried to dig it a little deeper. To delete the feature through ArcGIS Server’s rest interface. Finally an error message appeared. Code 10500, ‘Database error has occurred’, it said.
image

The next step I went was checking the log files of the ArcGIS Data Store. It didn’t take long until I found the following suspicious lines.

2017-01-31 16:13:02 AEST: [5328]: [1-1] ERROR:  date/time field value out of range: "12.31.9999 23:59:59" at character 164
2017-01-31 16:13:02 AEST: [5328]: [2-1] HINT:  Perhaps you need a different "datestyle" setting.
2017-01-31 16:13:02 AEST: [5328]: [3-1] QUERY:  UPDATE hsu_9ugai.registered_sites_registered_sites SET gdb_to_date  = current_timestamp(3) AT TIME ZONE 'UTC'  WHERE objectid = old.objectid AND gdb_to_date = '12.31.9999 23:59:59'
2017-01-31 16:13:02 AEST: [5328]: [4-1] CONTEXT:  PL/pgSQL function nvv_update_78() line 15 at SQL statement
2017-01-31 16:13:02 AEST: [5328]: [5-1] STATEMENT:  DELETE FROM db_jq287.hsu_9ugai.registered_sites_registered_sites_evw WHERE objectid = $1

So an ‘date/time field value out of range’ error with a hint of solution appeared in the log file. Since ArcGIS Data Store stores data in a POSTGRESQL database. A quick GOOGLE search with the keywords would help to find the solution. The log file says ‘date/time field value out of range’ and then indicated that the out of range value is ‘12.31.9999 23:59:59’. Find the ArcGIS Data Store configuration folder, in pgdata folder, open postgresql.conf file. Search ‘datestyle’, the default setting is datestyle = ‘iso, dmy’. If you compare the format with the out of range date/time value, it is obvious they don’t match. The out of range date/time value format is ‘month.date.year’ while the ArcGIS Data Store default datestyle setting is ‘date.month.year’. That explained why the error had occurred.

The Solution

Simply change the datastyle value from ‘iso, dmy’ to ‘iso, mdy’, save the configuration file and restart ArcGIS Data Store service. Problem resolved.