nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Strange date output from ArcGIS Online feature layer using ArcGIS API for Python

TransCanadaTrailTCT

I'm trying to extract field value from an ArcGIS Online feature layer using the ArcGIS API for Python.

In ArcGIS Online, the date is displayed like 10/9/2018 10:30 AM (Field type = DATE)

From the Spatially Enabled Panda DataFrame, the date looks like 1.5390954e+18.

When I try to copy the date in a File Geodatabase (InsertCursor) from the DataFrame into a DATE field, I'm getting this error:

RuntimeError: The value type is incompatible with the field type.

How can I convert the 1.5390954e+18 formatted date to a proper date format to be insert in a DATE field?

Find more posts tagged with

Accepted answers

RandyBurton

It appears to be a unix timestamp in nanoseconds.

Scientific notation
1.5390954e+18
+18 move decimal 18 places to right

Timestamp in nanoseconds
1539095400000000000

Converts to UTC as:
Tuesday, October 9, 2018 2:30:00 PM
‍‍‍‍‍‍‍‍‍<SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

If you convert the UTC for your timezone, it should match. AGOL usually stores the date as timestamp; but I would have expected milliseconds.

<SPAN class="keyword token">from</SPAN> datetime <SPAN class="keyword token">import</SPAN> datetime
<SPAN class="keyword token">from</SPAN> dateutil <SPAN class="keyword token">import</SPAN> tz
<SPAN class="keyword token">from</SPAN> pytz <SPAN class="keyword token">import</SPAN> timezone

ts <SPAN class="operator token">=</SPAN> float<SPAN class="punctuation token">(</SPAN><SPAN class="string token">"1.5390954e+18"</SPAN><SPAN class="punctuation token">)</SPAN>

UTC <SPAN class="operator token">=</SPAN> datetime<SPAN class="punctuation token">.</SPAN>utcfromtimestamp<SPAN class="punctuation token">(</SPAN>ts <SPAN class="operator token">//</SPAN> <SPAN class="number token">1000000000</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">.</SPAN>replace<SPAN class="punctuation token">(</SPAN>tzinfo<SPAN class="operator token">=</SPAN>tz<SPAN class="punctuation token">.</SPAN>tzutc<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN>
<SPAN class="keyword token">print</SPAN> UTC
<SPAN class="comment token"># 2018-10-09 14:30:00+00:00</SPAN>

east <SPAN class="operator token">=</SPAN> UTC<SPAN class="punctuation token">.</SPAN>astimezone<SPAN class="punctuation token">(</SPAN>timezone<SPAN class="punctuation token">(</SPAN><SPAN class="string token">'Canada/Eastern'</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN>
<SPAN class="keyword token">print</SPAN> east
<SPAN class="comment token"># 2018-10-09 10:30:00-04:00</SPAN><SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

All comments

TransCanadaTrailTCT

Thanks Randy. You nailed it. It's working now. I never thought to get a date in this format.

bixb0012

The code you are using is pretty boilerplate, so nothing seems odd there. Plus, you mention other layers work fine. At this point, it seems to be something in the translation/upload from Pro to AGOL. It might be worth opening a case with Esri Support to dive deeper.

TransCanadaTrailTCT

I'm not sure if it's the way that I'm querying the feature layers, because I'm using the same process for other layers and it's working well. The only thing about this layer is the layer have not been created directly from ArcGIS Online, but it's an upload from ArcGIS Pro (SDE Geodatabase). Here is the code

<SPAN class="keyword token">from</SPAN> arcgis<SPAN class="punctuation token">.</SPAN>gis <SPAN class="keyword token">import</SPAN> GIS
<SPAN class="keyword token">from</SPAN> arcgis<SPAN class="punctuation token">.</SPAN>features <SPAN class="keyword token">import</SPAN> GeoAccessor<SPAN class="punctuation token">,</SPAN> GeoSeriesAccessor
<SPAN class="keyword token">import</SPAN> pandas <SPAN class="keyword token">as</SPAN> pd


item <SPAN class="operator token">=</SPAN> gis<SPAN class="punctuation token">.</SPAN>content<SPAN class="punctuation token">.</SPAN>get<SPAN class="punctuation token">(</SPAN><SPAN class="string token">"id_item"</SPAN><SPAN class="punctuation token">)</SPAN>
agol_layer <SPAN class="operator token">=</SPAN> item<SPAN class="punctuation token">.</SPAN>layers<SPAN class="punctuation token">[</SPAN><SPAN class="number token">0</SPAN><SPAN class="punctuation token">]</SPAN>

records <SPAN class="operator token">=</SPAN> pd<SPAN class="punctuation token">.</SPAN>DataFrame<SPAN class="punctuation token">.</SPAN>spatial<SPAN class="punctuation token">.</SPAN>from_layer<SPAN class="punctuation token">(</SPAN>agol_layer<SPAN class="punctuation token">)</SPAN><SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

bixb0012

TransCanadaTrail TCT‌, I just did some querying of feature layers using the ArcGIS API for Python, and I am not seeing the date behavior you are seeing. It may be your issue is with the spatially-enabled data frame. Can you show a code example of how you are creating the data frame?

RandyBurton

It appears to be a unix timestamp in nanoseconds.

Scientific notation
1.5390954e+18
+18 move decimal 18 places to right

Timestamp in nanoseconds
1539095400000000000

Converts to UTC as:
Tuesday, October 9, 2018 2:30:00 PM
‍‍‍‍‍‍‍‍‍<SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

If you convert the UTC for your timezone, it should match. AGOL usually stores the date as timestamp; but I would have expected milliseconds.

<SPAN class="keyword token">from</SPAN> datetime <SPAN class="keyword token">import</SPAN> datetime
<SPAN class="keyword token">from</SPAN> dateutil <SPAN class="keyword token">import</SPAN> tz
<SPAN class="keyword token">from</SPAN> pytz <SPAN class="keyword token">import</SPAN> timezone

ts <SPAN class="operator token">=</SPAN> float<SPAN class="punctuation token">(</SPAN><SPAN class="string token">"1.5390954e+18"</SPAN><SPAN class="punctuation token">)</SPAN>

UTC <SPAN class="operator token">=</SPAN> datetime<SPAN class="punctuation token">.</SPAN>utcfromtimestamp<SPAN class="punctuation token">(</SPAN>ts <SPAN class="operator token">//</SPAN> <SPAN class="number token">1000000000</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">.</SPAN>replace<SPAN class="punctuation token">(</SPAN>tzinfo<SPAN class="operator token">=</SPAN>tz<SPAN class="punctuation token">.</SPAN>tzutc<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN>
<SPAN class="keyword token">print</SPAN> UTC
<SPAN class="comment token"># 2018-10-09 14:30:00+00:00</SPAN>

east <SPAN class="operator token">=</SPAN> UTC<SPAN class="punctuation token">.</SPAN>astimezone<SPAN class="punctuation token">(</SPAN>timezone<SPAN class="punctuation token">(</SPAN><SPAN class="string token">'Canada/Eastern'</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN>
<SPAN class="keyword token">print</SPAN> east
<SPAN class="comment token"># 2018-10-09 10:30:00-04:00</SPAN><SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.