Date extraction issue

Question

Hello,

I'm using ArcPro 2.8 and want to summarize a table of 30 years of forest prescribed burn data into sums of acres burn each month on each tract of land. Initially we only had the burn date in m/d/yy and I followed the instructions on "How To: Extract and insert a portion of the date field in a new field in ArcGIS Pro" and was able to extract the month and year to be able to summarize the way I needed. Note I also preformed the transformation of adding one to the month so instead of January=0, January=1

Once I began confirming the data I noticed that dates that fell on the first of a month were extracted into the previous month. For example: date=3/1/2021, extracted month=2

So then I extracted Day so I could sort by day to highlight the issue but that also was extracted as the previous day with the first of the month showing as the last day of the previous month.

See screengrab below.

Any ideas on cause and corrections?

Thanks

DavidPike · Answer

I'd say the shapefile explains it.  Can you zip up a geodatabase and use that instead?

I'm not even sure what the object type is, maybe a unicode string that AGOL does some 'magic' with?

I might try and keep it simple and just try and do some string formatting to grab the numbers between the slashes.

completely untested and unsure of what Text(yourDate) will give you, but something like:

var dateString = Text(yourDate) --> var splitDate = Split(dateString, '/') --> var month = splitDate[0]

JonHoch · Answer

The dataset is a shapefile; which I believe I read doesn't save a time with the date unlike a database that would save a default time along with the date.

Unfortunately ToLocal() didn't work. I also extracted Day so I could sort to find all the errors and again all the records for the first of the month are extracted incorrectly and strangely enough if the prior month had 31 days it returns a value of 32! Additionally if the Date is January 1st it comes back as December 32nd of the prior year.

DavidPike · Answer

There's probably a local windows conversion of that time display going on, what is the dataset? I'm guessing its a database?

It looks very much like a UTC and local timezone problem, i.e. the UTC time isn't localised before it's used in the Month() function.  I'd bet using ToLocal() on your timestamp when it's initially referenced would solve everything  https://developers.arcgis.com/arcade/function-reference/date_functions/#tolocal

Month(ToLocal($feature.datefield))

but you need to properly test this.