[
https://issues.apache.org/jira/browse/HIVE-14412?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15527345#comment-15527345
]
Jason Dere commented on HIVE-14412:
-----------------------------------
Thanks for the patch. Had a few questions on the conversions:
Date to TimestampTZ: Converted to yyyy-mm-dd 00:00:00, in the local TZ
date ‘1970-01-01’ => timestamp_tz ‘1970-01-01 00:00:00 GMT-08:00'
Timestamp to TimestampTZ: Converted to yyyy-mm-dd HH:MM:SS, in local TZ
timestamp '1970-01-01 00:00:00’ => timestamp_tz '1970-01-01 00:00:00 GMT-08:00'
TimestampTZ to Date:
timestamp_tz “1970-01-01 00:00:00 GMT-05:00” => date “1970-01-01"
TimestampTZ to Timestamp:
timestamp_tz“1970-01-01 00:00:00 GMT-05:00” => timestamp “1970-01-01 00:00:00”
I think this matches the SQL spec conversion behavior, but would be good for
someone to double check - [~alangates] or anyone else?
{quote}
For the convenience of users, whenever a datetime value with time zone is to be
implicitly derived from one without (for example, in a simple assignment
operation), SQL assumes the value without time zone to be local, subtracts the
current default time zone displacement of the SQL-session from it to give UTC,
and associates that time zone displacement with the result.
Conversely, whenever a datetime value without time zone is to be implicitly
derived from one with, SQL assumes the value with time zone to be UTC, adds the
time zone displacement to it to give local time, and the result, without any
time zone displacement, is local.
{quote}
Is there a way to convert TimestampTZ to a different TZ?
“1970-01-01 00:00:00 GMT” => “1969-12-31 16:00:00 GMT-08:00”
Comparison (equality, mim/max). Here both the spec, as well as Oracle
(https://docs.oracle.com/cd/B19306_01/server.102/b14225/ch4datetime.htm#i1006081),
mention that the time zone should not be used for comparison, just the instant
in UTC.
“1970-01-01 00:00:00 GMT” == “1969-12-31 16:00:00”
Is there way to keep non-numeric timezones, like ‘America/Los_Angeles’ rather
than ‘GMT-08:00'?
> 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.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
>
>
> 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.4#6332)
