topic Re: Uploading Excel Document, Data Field is Off by a Day in ArcGIS Enterprise Questions

Uploading Excel Document, Data Field is Off by a Day

CarrieQuast — Thu, 19 Nov 2020 22:25:32 GMT

Hello Everyone - I have a really strange problem.

I need to upload an Excel Spreadsheet with a Date Field, as well as a number of Text Fields to ArcGIS Enterprise. When I upload it, the date decreases by a day, and it always adds a time of 7pm. My data has no time information.

Here is what I've tried:

- turning the spreadsheet into a CSV document - no change in output

-Messing with the time zone on the upload in enterprise server (eg trying one that should add or subtract to get the correct hours). No go, and it still says one day earlier, at 7pm regardless of time zone.

-Checking the time on the server enterprise sits on.

-Going through on ESRI enterprise manager, see if there is an obvious setting somewhere.

Now, I can easily create a fix for this, but it will be a daily upload by my user who doesn't have time to mess with dates, nor the knowledge to do so in excel. It is also correct when I upload it into ArcGIS Pro and ArcGIS Map.

Here is a screen grab - Enterprise on the left, Excel on the right.

I'm running Arcgis 10.6

Thank You for your help

Re: Uploading Excel Document, Data Field is Off by a Day

DavidPike — Thu, 19 Nov 2020 22:48:05 GMT

I guess it's taking it in as assumed UTC (without specifying a timestamp it assumes 00:00:00 hrs) then converting to your local time (UTC -5 hours a good guess?).

That's strange, as the behaviour I observed on my enterprise when editing was that local times specified in edits were then posted as UTC to the enterprise gdb fc.

appreciate there's no solution in my reply, just be warmed by the knowledge that I feel your pain.

Re: Uploading Excel Document, Data Field is Off by a Day

JayJohnsonWashoeCounty — Thu, 19 Nov 2020 23:24:48 GMT

UTC is more often than not a pain. I've ended up creating date fields specifically to store our local time and having to calculate the offset. Or, store the date or the year as a text field (I know that is a cardinal sin against the data gods, for a variety of reasons). Yes, web apps will do on-the-fly conversion to local time based on the viewing device, but if you are doing database queries not in a web environment you have to do extra steps to get what you want. And yes, maybe if you go Portal you can avoid UTC(?).

I do appreciate WHY Esri did this -- and I'm sure if I worked for a multi-national conglomerate that operated in five different time zones I'd think UTC was great. But for the majority of Esri users it is a flaming hoop through which to jump.

Okay, I feel better now. 🙂