arrow-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Greg Rahn (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (ARROW-637) [Format] Add time zone metadata to Timestamp type
Date Fri, 17 Mar 2017 20:43:41 GMT

    [ https://issues.apache.org/jira/browse/ARROW-637?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15930689#comment-15930689
] 

Greg Rahn commented on ARROW-637:
---------------------------------

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:
{noformat}
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
{noformat}


> [Format] Add time zone metadata to Timestamp type
> -------------------------------------------------
>
>                 Key: ARROW-637
>                 URL: https://issues.apache.org/jira/browse/ARROW-637
>             Project: Apache Arrow
>          Issue Type: New Feature
>          Components: Format
>            Reporter: Wes McKinney
>            Assignee: Wes McKinney
>
> 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. 
> https://github.com/apache/arrow/pull/388



--
This message was sent by Atlassian JIRA
(v6.3.15#6346)

Mime
View raw message