`to_featurelayer` and `append` do not maintain DF's datetime

[cgpeltier]

trafficstars

Describe the bug Hi team! This follows up on a conversation I had with @nanaeaubry at the UC!

My goal is ultimately to ensure that my datetime columns are not erroneously converted to a different timezone when publishing a layer to AGOL. For example, if I have a date column with the date "7-15-2022", pushing it to AGOL using to_featurelayer() will convert it to "7/14/2022, 8:00 PM" due to the UTC conversion (if I am located in EST).

I attempted to mitigate this by using the datetime and pytz modules to change my datetimes before publishing my spatially enabled dataframe. However, converting my date column to be datetime64[ns, UTC] does not affect the UTC conversion when pushing to AGOL. Further, other datetime dtypes like datetime64[ns, US/Eastern] are converted in the same way, suggesting that these dtypes are not used by to_featurelayer.

This also happens using the .append method.

To Reproduce Steps to reproduce the behavior:

import pandas as pd
from datetime import date, timedelta
from datetime import datetime
import requests
from pytz import timezone

import arcgis
from arcgis.gis import GIS
gis = GIS()
from arcgis.features import GeoAccessor

format = "%Y-%m-%d %H:%M:%S"
today = datetime.today().strftime('%Y-%m-%d')
now = datetime.now().strftime(format)

df = pd.DataFrame([
    {'test' : 'test1',
     'longitude' : -117.15246191281217,
     'latitude' : 32.70969794547845,
     'date' : str(today)},
     {'test' : 'test2',
     'longitude' : -115.15246191281217,
     'latitude' : 32.70969794547845,
     'date' : str(today)},
     {'test' : 'test3',
     'longitude' : -117.15246191281217,
     'latitude' : 34.70969794547845,
     'date' : str(today)},
    ])

## create timezone cols
df['date_datetime'] = pd.to_datetime(df['date'])
df['date_datetime_utc'] = pd.to_datetime(df['date'], utc=True)
df['date_loc_utc'] = df['date_datetime'].dt.tz_localize('UTC')
df['date_loc_est'] = df['date_loc_utc'].dt.tz_convert('US/Eastern')
df['date_loc_pst'] = df['date_loc_utc'].dt.tz_convert('US/Pacific')

sedf = pd.DataFrame.spatial.from_xy(df=df, x_column='longitude', y_column='latitude', sr=4326)
print(sedf.dtypes)

## push to AGOL
sedf.spatial.to_featurelayer('agol_dt_test_{}'.format(now), gis=gis)

## append new data 
format = "%Y-%m-%d %H:%M:%S"
today = datetime.today().strftime('%Y-%m-%d')

df = pd.DataFrame([
    {'test' : 'update1',
     'longitude' : -117.15246191281217,
     'latitude' : 32.70969794547845,
     'date' : today, #str(datetime.now().strftime(format))
     },
     {'test' : 'update2',
     'longitude' : -115.15246191281217,
     'latitude' : 32.70969794547845,
     'date' : today, #str(datetime.now().strftime(format))
     },
     {'test' : 'update3',
     'longitude' : -117.15246191281217,
     'latitude' : 34.70969794547845,
     'date' : today, #str(datetime.now().strftime(format))
     },
    ])

## create timezone cols
df['date_datetime'] = pd.to_datetime(df['date'])
df['date_datetime_utc'] = pd.to_datetime(df['date'], utc=True)
df['date_loc_utc'] = df['date_datetime'].dt.tz_localize('UTC')
df['date_loc_est'] = df['date_loc_utc'].dt.tz_convert('US/Eastern')
df['date_loc_pst'] = df['date_loc_utc'].dt.tz_convert('US/Pacific')

update_with = pd.DataFrame.spatial.from_xy(df=df, x_column='longitude', y_column='latitude', sr=4326)

## add new SEDF to AGOL as a feature layer
ofm_update = update_with.spatial.to_featurelayer('agol_test_update_{}'.format(now), gis=gis)
print(ofm_update.id)

## save new layer to AGOL as GDB 
ofm_update2 = ofm_update.export('test', 'File Geodatabase')
print(ofm_update2.id)

## append to existing feature layer
to_update = gis.content.get(itemid=item_id) 
print(to_update.id)

to_update.layers[0].append(
    item_id = ofm_update2.id,
    upload_format = 'filegdb', 
    upsert = False,
)

Screenshots

Expected behavior Datetimes are preserved in AGOL, so my date_datetime and date_datetime_utc columns should both be 07-15-2022 12:00 AM in AGOL.

Platform (please complete the following information):

• OS: Windows
• Browser Chrome/Firefox
• Python API Version 2.0.0

[achapkowski]

@cgpeltier to_featurelayer is using arcpy.da.InsertCursor on the backend, and I have triaged it down to the arcpy level issue. I am in contact with the arcpy team. I'll let you know what I find out.

[cgpeltier]

Hey @achapkowski just wanted to check in on this one - thanks! 😄

[nanaeaubry]

Hi! We got some update from arcpy that at the next release timezones will be supported in the InsertCursor of arcpy, in addition we've made updates on our end that will also help with this.

We have a beta release in July that you can test out!

[cgpeltier]

Awesome stuff, thanks for the update!

[cgpeltier]

Btw, in case it's helpful for anyone else, prior to the July beta release that was mentioned above, I have been doing the following workaround:

• Create a new feature layer with .to_featurelayer() in an environment with arcpy so that it loads as a FGDB.
• Edit the DataFrame of features using pd.to_datetime() with utc=False
• Convert the DataFrame to featuresets in chunks of ~100-150 rows of the DF
• Use layer.edit_features(updates=fs)

This process of using featuresets to update the existing layer features should preserve the correct TZ in your hosted feature layer!