[Format] Support an official "timestamp with time zone offset" type

[CurtHagenlocher]

Describe the enhancement requested

Relational databases Snowflake, MSSQL, Oracle, Teradata, and SAP SQL Anywhere all support a data type which stores both a timestamp and a time zone offset. This differs from the existing Arrow timestamp type by letting each individual value in the column have a different offset and by not being tied to a geopolitical time zone. This type also appears in Java as OffsetDateTime and in .NET as DateTimeOffset. It would be nice given how commonly it appears if there were a standard way to represent this in Arrow.

This could be done as an extension type for a structure consisting of separate 8-byte timestamp and 2-byte offset values, or as a new first-class type. Intervals are a structure with some similarity to this type and were done as a first-class type, but they also predate the extension type mechanism.

Component(s)

Format

[rok]

For the record arrow can currently store timezone offsets per array as strings see here.
To store per value offsets an extension type sounds like a good idea. What temporal resolution would you propose? Minutes would fit into two bytes I suppose.

[CurtHagenlocher]

The standard seems to be support for minute-level resolution with a range of something like -14:00 to +14:00. Storing the number of minutes as an int16 seems right.

[rok]

Adding an extension type would start by opening a PR against CanonicalExtensions.rst describing the proposed type and calling for discussion/vote on the ML (e.g. 8-bit boolean). It might make sense to wait for more people to chime in before doing so though.

[CurtHagenlocher]

Related request: apache/arrow-java#171

[aiguofer]

Interesting, my request ^ was slightly unrelated. However, I've been doing some TZ work recently and would love to have a vector type that includes the offset/tz per-row instead of the existing type that includes the offset/tz for the entire vector.