Support the DateTimeOffset and TIMESTAMP WITH TIME ZONE column data types

[GeraldMarbury]

Because mobile  and browser users move between time zones as they create data, the nature of data in or age is that each row of a table can be collected in a different time zone.  Because storing all dates as UTC is not always desirable, I suggest ESRI support the various date time data types that are time zone aware so that each date value in a row can know what time zone it was collected in. Time zone aware data types already exists in Oracle, SQL Server , PostgreSQL  and I suspect other RDMS that the Geodatabase supports.

Sql Server: datetimeoffset (Transact-SQL)

Oracle: Datetime Datatypes and Time Zone Support ),

PostgreSQL: PostgreSQL: Documentation: 8.2: Date/Time Types

[KellyGerrow]

Thanks for submitting this idea!

Can you provide some additional information about your workflow? How is the data viewed that is collected in different time zones? Are you looking to have the data displayed in specific time zones or track in which time zone the data is collected?

-Kelly

[GeraldMarbury]

Actually, this allows you to support both when it was collected and display it. Imagine a hypothetical hosted bill collection app that requires the power bill be paid to the remote technician by 5PM local time to keep from being disconnected. If you operate techs in Alabama and Georgia, that will be different time zones depending on where the technician was at the time. The mobile device will automatically change timezones when they cross the state line but the app can't store this information unless it uses two fields (Device Time and Time Zone) or a text field in ISO 8601 date.  Both of these of course makes reporting, sorting and query more complicated.  Storing the time as UTC is not enough either because depending on if the work was in Alabama or Georgia the due date may not have expired given the same UTC time. The three formats I listed above already handle this for the major RDMS.

A related problem is that the ArcGIS REST endpoints use javascript numeric based dates to transport the data. This assumes all dates are UTC. See this issue for example.

Problem: Date fields in a web map display incorrectly in the ArcGIS Online map viewer 

Dates should have the option to be transported as ISO 8601 strings instead of numeric so that time zone can be transported for each record.

[SSWoodward]

Thanks to everyone who participated in this conversation!

In the newly released ArcGIS Pro 3.2, users have access to a brand new field data type, Timestamp Offset

This field data type is currently supported in file and mobile geodatabase, as well as enterprise geodatabases in Oracle and SQL.

For more about what's new from the geodatabase team, check out our what's new in 3.2 post.

What's new in 3.2 from geodatabase

For a bigger picture view, check out our general what's new in ArcGIS Pro 3.2

What's new in 3.2 - ArcGIS Pro