[Format] Add time zone metadata to Timestamp type

[asfimport]

As a metadata-only convenience, it would be useful to have an optional Olson time zone name or absolute time offset (e.g. +07:30) in the Timestamp flatbuffers type: https://github.com/apache/arrow/blob/master/format/Message.fbs#L94

Null or length-0 string would indicate that the data is time zone naive, and shall not be considered to be localized.

#388

Reporter: Wes McKinney / @wesm
Assignee: Wes McKinney / @wesm

Related issues:

• Add integration tests for date and time types (blocks)
• [Java] Support additional Timestamp timezone metadata (relates to)
• Add "Floating Timestamp" logical type (relates to)
• Finalize timestamp type (is related to)

_{Note: This issue was originally created as ARROW-637. Please see the migration documentation for further details.}

[asfimport]

Julien Le Dem / @julienledem:
Sounds good to me with the following tweak:

• timezone field present means data stored in that timezone (including UTC). Which means when displayed to the user it can be converted to the display timezone.
• timezone field missing means the data is timezone-less similar to the SQL behavior. a Timestamp without timezone should be printed exactly the same (as if we had stored the string representing it) independently of the "client" timezone. It is stored as UTC but does not get converted to the user timezone.

[asfimport]

Wes McKinney / @wesm:
I agree. I opened a PR reflecting this #388 – it would be good to get the perspective of [~julianhyde] or others more familiar with SQL implementations to make sure we're capturing the semantics correctly.

[asfimport]

Julian Hyde:
When you say a "timezone in the Timestamp flatbuffers type" do you mean that the timezone is stored in the type, or in the value? If you have 1M values, would they each have their own timezone?

If it's stored in the type, then @julienledem is correct that it would be a good way to emulate SQL's TIMESTAMP type. Just store UTC in the type.

If the timeonze is stored in the value, then you have re-created SQL's TIMESTAMP WITH TIMEZONE type. Think of each value consisting of a (milliseconds, timezone) pair. Note that this is much more rarely used, and lower priority IMHO. (PostgreSQL has a TIMESTAMP WITH TIMEZONE type but they botched it – they don't store a timezone, so it is interpreted as an Instant, similar to java.util.Date. I do see value in having an "instant" type, in addition to a "zoneless timestamp" type, just don't confuse everyone by calling it "timestamp with timezone".)

I don't know whether offsets, e.g. "+7:30" are valid as Olson timezone names, but you should allow these. SQL's TIMESTAMP WITH TIMEZONE type uses offsets, not timezone names, IIRC.

[asfimport]

Wes McKinney / @wesm:
[~julianhyde] This is a metadata-only time zone, so a single timezone for the entire array, not a different timezone for each value. I've occasionally seen demand for having a time zone for each value but I don't think it's high priority.

If you can look at the changes in https://github.com/apache/arrow/pull/388/files#diff-520b20e87eb508faa3cc7aa9855030d7R97 and confirm this looks semantically correct

[asfimport]

Julian Hyde:
Looks good. Except that you should support offsets as well as timezone names. That probably just means changing the comment at this point.

[asfimport]

Wes McKinney / @wesm:
Got it. Updating comment now

[asfimport]

Julian Hyde:
One last quibble. You wrote

Null or length-0 string would indicate that the data has no time zone (UTC)

but I would correct it to

Null or length-0 string would indicate that the data has no time zone

UTC is a time zone. "No time zone" is different; it means that the user needs to bring their own interpretation.

[asfimport]

Wes McKinney / @wesm:
You're right, I corrected the JIRA description – this is correctly stated in the patch #388

[asfimport]

Greg Rahn:
Just to comment: most SQL implementations of TIMESTAMP WITH TIME ZONE do not store timezone info (AFAIK, just Oracle's TIMESTAMP WITH TIME ZONE), however, the data is normalized to UTC when stored. This means that a stored value of 0 would return 1970-01-01 00:00 (or in PostgreSQL year 2000 since their epoch is different than unix time) for a w/o timezone timestamp, but the same value of 0 would return 1970-01-01 00:00Z adjusted for local time zone settings if the type were "with time zone".

For example:

grahn=# create table ts (
grahn(#   ts_string varchar(50),
grahn(#   ts_timestamp timestamp without time zone,
grahn(#   ts_timestamptz timestamp with time zone
grahn(# );

grahn=# insert into ts values (
grahn(#   '2016-01-01 00:00:00+10'::varchar,
grahn(#   '2016-01-01 00:00:00+10'::timestamp,
grahn(#   '2016-01-01 00:00:00+10'::timestamptz
grahn(# );

grahn=# show timezone;
  TimeZone
------------
 US/Pacific

grahn=# select * from ts;
       ts_string        |    ts_timestamp     |     ts_timestamptz
------------------------+---------------------+------------------------
 2016-01-01 00:00:00+10 | 2016-01-01 00:00:00 | 2015-12-31 06:00:00-08

grahn=# set time zone 'Europe/Rome';
grahn=# show timezone;
  TimeZone
-------------
 Europe/Rome

grahn=# select * from ts;
       ts_string        |    ts_timestamp     |     ts_timestamptz
------------------------+---------------------+------------------------
 2016-01-01 00:00:00+10 | 2016-01-01 00:00:00 | 2015-12-31 15:00:00+01

[asfimport]

Julian Hyde:
[~grahn], I know that PostgreSQL's "timestamp with time zone" is basically an "instant". Thanks for confirming.

It wasn't clear in your comment whether Oracle does the correct thing (per the SQL standard) and stores a time zone value. Can you clarify?

As far as I know Teradata and DB2 do the right thing. And I don't know of any other DBs that support it.

As an aside: my biggest gripe with PostgreSQL is that they called their type "TIMESTAMP WITH TIME ZONE". Their type is a useful one to have in any DBMS, since it matches what C/UNIX and Java developers are used to. I just wish they'd called it "INSTANT".

[asfimport]

Julien Le Dem / @julienledem:
Issue resolved by pull request 388
#388

[asfimport]

Greg Rahn:
[~julianhyde] - Oracle has 3 timestamp types (from https://docs.oracle.com/database/121/SQLRF/sql_elements001.htm#SQLRF0021)

• timestamp w/o time zone
• timestamp with local time zone
  • Data is normalized to the database time zone when it is stored in the database.
  • When the data is retrieved, users see the data in the session time zone.
• timestamp with time zone
  • Data is stored with an explicit time zone, but not adjusted/normalized for local time zone.
  • Value returned contains time zone offset information.

The ANSI and Oracle w/o TZ versions are the same, but the ANSI with TZ aligns more to Oracle's with LOCAL TZ.

create table ts (
  ts_string        varchar(50),
  ts_timestamp     timestamp,
  ts_timestamp_tz  timestamp with time zone,
  ts_timestamp_ltz timestamp with local time zone
);

insert into ts values (
  '2016-01-01 00:00:00 +10:00',
  timestamp '2016-01-01 00:00:00 +10:00',
  timestamp '2016-01-01 00:00:00 +10:00',
  timestamp '2016-01-01 00:00:00 +10:00'
);

alter session set NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SSXFF';
alter session set NLS_TIMESTAMP_TZ_FORMAT = 'YYYY-MM-DD HH24:MI:SSXFF TZR';

select dbtimezone from dual;

DBTIME
------
+00:00

select sessiontimezone from dual;

SESSIONTIMEZONE
---------------
-05:00

select * from ts;

TS_STRING                   TS_TIMESTAMP                TS_TIMESTAMP_TZ                    TS_TIMESTAMP_LTZ
--------------------------  --------------------------  ---------------------------------  --------------------------
2016-01-01 00:00:00 +10:00  2016-01-01 00:00:00.000000  2016-01-01 00:00:00.000000 +10:00  2015-12-31 09:00:00.000000

[asfimport]

Wes McKinney / @wesm:
So it seems that we are well covered for if we have the two notions of:

• Time zone naive data (w/o time zone) – no localization will occur when displaying these time stamps to the user
• Time zone aware data: data is internally normalized to UTC, but can be displayed as a localized timestamp to the user

I spent a bunch of time on this in pandas in 2012, here is how things work there:

In [4]: import pandas as pd

In [5]: stamp = pd.Timestamp('2000-01-01 00:00:00')

In [6]: stamp.value  # nanoseconds
Out[6]: 946684800000000000

In [7]: stamp_as_utc = stamp.tz_localize('utc')

In [8]: stamp_as_utc
Out[8]: Timestamp('2000-01-01 00:00:00+0000', tz='UTC')

In [9]: stamp_as_utc.value
Out[9]: 946684800000000000

In [10]: stamp_as_eastern = stamp_as_utc.tz_convert('America/New_York')

In [11]: stamp_as_eastern
Out[11]: Timestamp('1999-12-31 19:00:00-0500', tz='America/New_York')

In [12]: stamp_as_eastern.value
Out[12]: 946684800000000000

In [13]: stamp_as_eastern.year
Out[13]: 1999

In [14]: stamp_as_eastern.month
Out[14]: 12

In [15]: stamp_as_eastern.hour
Out[15]: 19

Here, you can see the physical int64 value is the same in all cases, but we have 3 different notions: tz-naive (stamp has no time zone), with UTC, with a particular local time zone – but the tz is used for display only and computing day/month/year/hms components

[asfimport]

Julian Hyde:
I agree with @wesm; Arrow is in good shape.

Thank you, [~grahn], for your clear explanation of Oracle's semantics.