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 Sat, 18 Mar 2017 17:59:41 GMT

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

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

[~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.  

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