[
https://issues.apache.org/jira/browse/HIVE-14412?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16000251#comment-16000251
]
Carter Shanklin commented on HIVE-14412:
----------------------------------------
I tried the patch and had a few comments, 1 relates to semantics and 2 relate
to usability.
Semantics:
SQL has a notion of session default time zone displacement which is used in
various conversions. I was not able to find any way to control this in Hive, I
tried changing my system time zone but this didn't have any effect. It appeared
Hive behaves as if the session time zone is GMT.
This becomes a problem when you convert from dates or timestamps without
timezones into timestamps with timezones, per the SQL standard the session zone
displacement is supposed to be taken into account. A date recorded as timestamp
with time zone in London needs to occur earlier in time than that same date
converted in California. I don't see any way to do that currently, did I miss
it?
Ideally you should be able to use the standard approaches:
set time zone '-0:00';
set time zone '-8:00';
set time zone LOCAL;
And maybe extensions like:
set time zone 'GMT'; etc.
At a minimum you should be able to control it somehow, for example through
system time zone.
For usability:
1. These SQL standard formats are not accepted:
select cast('2012-01-01 00:00:00' as timestamp with time zone);
select cast('2012-01-01 00:00:00 -6:00' as timestamp with time zone);
These both return NULL.
2. The input is extremely strict:
These common inputs also return NULL.
select cast('2012-01-01' as timestamp with time zone);
select cast('2012-01-01 00:00:00 PST' as timestamp with time zone);
select cast('2012-01-01 00:00:00Z' as timestamp with time zone);
Even with the format recognized, things remain very strict:
select cast('2012-01-01 00:00:00 GMT-06:00' as timestamp with time zone); <-
works
select cast('2012-01-01 00:00:00 GMT-6:00' as timestamp with time zone); <-
returns null
The other big usability issue is there is no way to convert to a target time
zone. SQL allows for things like select tswtz at time zone '-08:00' to convert
to a known time zone. It's important but could probably be handled as a follow
up.
> Add a timezone-aware timestamp
> ------------------------------
>
> Key: HIVE-14412
> URL: https://issues.apache.org/jira/browse/HIVE-14412
> Project: Hive
> Issue Type: Sub-task
> Components: Hive
> Reporter: Rui Li
> Assignee: Rui Li
> Attachments: HIVE-14412.10.patch, HIVE-14412.11.patch,
> HIVE-14412.1.patch, HIVE-14412.2.patch, HIVE-14412.3.patch,
> HIVE-14412.4.patch, HIVE-14412.5.patch, HIVE-14412.6.patch,
> HIVE-14412.7.patch, HIVE-14412.8.patch, HIVE-14412.9.patch
>
>
> Java's Timestamp stores the time elapsed since the epoch. While it's by
> itself unambiguous, ambiguity comes when we parse a string into timestamp, or
> convert a timestamp to string, causing problems like HIVE-14305.
> To solve the issue, I think we should make timestamp aware of timezone.
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)
