• EN
Solved

Cannot write out 19700101000000 ESRI ArcGIS Portal Feature Service Writer

sammy
Rank
Userlevel 1
Badge +6

I am having a unique issue where I cannot write out the date 19700101000000 (01/01/1970) to my ‘Date’ data type fields in an Esri sde feature class. How do I fix this problem?

 

When I want to write out 19700101000000 to one of these fields, the writer runs successfully and I get the success message but then when I read the data back into FME or I check my attribute table in ArcGIS Pro the field shows up as 18991230000000 (12/30/1899).

 

The date is formatted correctly. I can write out other dates no problem. I can write out 19700101000001 and 19710101000000 no problem. I just can’t write out 19700101000000.

 

I can open the attribute table in ArcGIS Pro and manually enter the date as ‘1/1/1970 12:00:00 AM’ (from the calendar/time picker) and the table will accept it. I can also use calculate field and input '1970-01-01 00:00:00' (python format) and the table will update correctly.

 

I have tried using AttributeManager and DateTimeConverter to make sure the date is formatted correctly and the writer will run successfully but it still will not show up in the table correctly.

 

I am using FME Workbench 2023.1

My workflow is:

  • ESRI ArcGIS Portal Feature Service Readers to read in two tables (Table1 and Table2)
  • ChangeDetector to compare selected fields (to see what changed in Table1 to push updates to Table2)
  • ESRI ArcGIS Portal Feature Service Writer to write out the updates, inserts, deletes to Table2
    • Feature Operation: fme_db_operation
    • Feature Type Handling: Use Existing

Unfortunately I cannot share samples of the data or the tables as it is protected information.

icon

Best answer by geospatialize 16 May 2024, 19:15

View original

10 replies

sammy
Rank
Userlevel 1
Badge +6

@nampreetatsafe or anyone else… any advice on how else to troubleshoot and fix this?

panda
Rank
Badge +13

Try these if you havn’t already.

  1. Try read feature service through Feature Reader, and check on schema port. Verified that the field in question is Date field.
  2. Check the problematic feature and verified if that field somehow got convert to “text” instead of date
  3. In the writer that you use to write AGOL Feature service, under manual, you could force to put Date field there again.
  4. Last way I could think off is use schema scanner and set the schema again (have to make sure all field name spell correctly and what not, or it could create issues.)
sammy
Rank
Userlevel 1
Badge +6

A coworker of mine asked ChatGPT about this issue and the response from ChatGPT seems to suggest that this is a Unix Epoch Date/OLE Automation Date handling issue.

 

ChatGPT is saying that Unix & other systems use Jan 1 1970 00:00:00 as their 0/start datetime (Unix Epoch) while Microsoft & ArcGIS use Dec 30 1899 as their 0/start datetime (the OLE Automation Date).

So perhaps this is why when I try to write out Jan 1 1970 00:00:00 as a legitimate date from fme to esri at some point it is being interpreted as 0/null and so it shows up as Dec 30 1899 in my sde feature class.

 

I will try the suggestions above from @panda as well as some other suggestions from my coworker but I think this may have to be put in as a support ticket.

sammy
Rank
Userlevel 1
Badge +6

I tested the Esri Geodatabase (ArcSDE Geodb) writer and it wrote successfully and the date is showing up in the table correctly as 1/1/1970.

So using the Esri Geodatabase (ArcSDE Geodb) writer instead of the Esri ArcGIS Portal Feature Service writer is a possible solution if you have a direct database connection.

 

I am still hoping for a fix for the Esri ArcGIS Portal Feature Service writer as feature services are preferable for my use than a direct database connection.

geospatialize
Rank
Badge +3

Hi Sammy, I may have a solution to your issue.

If you only need the Month/Day/Year, I suggest using the DateTimeConverter transformer and selecting "%B %e, %Y" for the Output Format

If you do require the hours, minutes, and time then I suggest adding 1 second (19700101000001) and it should work.

Cheers!

sammy
Rank
Userlevel 1
Badge +6

@geospatialize Thank you. Changing the date to “%B %e, %Y” worked.

geospatialize
Rank
Badge +3

It seems like the issue arises from ESRI Portal and your local timezone.

Portal stores the date field using Epoch time, which means the earliest possible date is 1970/01/01. Anything before that is treated as invalid or, if configured otherwise, defaults to the 1899 date.

(I am located in Vancouver, which has an 8-hour difference from Greenwich Time.) When I write 19700101080000, I get the correct value stored in Portal.

(Colleague's machine located in Ontario.) When they write 19700101080000, the Portal time changes to 19700101030000.

For more information on ESRI Date, check this documentation: https://enterprise.arcgis.com/en/portal/latest/use/work-with-date-fields.htm.

I hope this gives you insight into this error. :)

Cheers!

sammy
Rank
Userlevel 1
Badge +6

@geospatialize What confuses me is that I am able to write out datetime values before that date also. I can write out 19680211000000 and it will show up correctly in the attribute table as 2/11/1968. So it seems to just be the specific 19700101000000 value.

geospatialize
Rank
Badge +3

My colleague and I are going to try to investigate it further.

The impression we got from that documentation is that it seems like anything before the epoch start will be treated as a negative value. It could be related to how ArcGIS Portal handles epoch time and interprets the value "19700101000000", specifically, as zero (or null).

My colleague says that to determine the root cause of the issue, we can test writing the date value '19700101000000' directly using the ArcGIS Portal REST API. That way, we can understand if it is an issue with FME or a bug in the ArcGIS Portal REST API.

If we find any useful information from investigating this issue, I will reply below so that others can also be informed.

geospatialize
Rank
Badge +3

We tried sending the date "19700101000000" from ArcGIS Pro to the Portal. Interestingly, we got the invalid date value of "12/29/1899 7:00:00pm". This makes us think that the issue might not be with FME but rather how Esri interprets and handles that particular date value. We think it might be worthwhile to reach out to Esri directly to clarify how dates are handled so you could potentially get some guidance on how to work around this quirk.

 

Cheers!

Reply


Community Stats

28,867
Posts
109,983
Replies
36,672
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.