[HIVE-16615] Support Time Zone Specifiers (i.e. "at time zone X")

• Type: Improvement
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Assignee: Bing Li
• Reporter: Carter Shanklin
• Created: 09/May/17 13:33
• Updated: 09/Jun/17 04:04

Description

HIVE-14412 introduces a timezone-aware timestamp.

SQL has a concept of "time zone specifier" which applies to any datetime value expression (which covers time/timestamp with and without timezones). Hive lacks a time type so we can put that aside for a while.

Examples:
a. select time_stamp_with_time_zone at time zone '-8:00';
b. select time_stamp_without_time_zone at time zone LOCAL;

These statements would adjust the expression from its original timezone into a known target timezone.

Using the time zone specifier results in a data type that has a time zone. If the original expression lacked a time zone, the result has a time zone. If the original expression had a time zone, the result still has a time zone, possibly a different one.

LOCAL means to use the session's original default time zone displacement.

The standard says that dates are not supported with time zone specifiers. It seems common to ignore this rule and allow this, by converting the date to a timestamp and then applying the usual rule.

The standard only requires an interval or the LOCAL keyword. Some databases allow time zone identifiers like PST.

Reference: SQL:2011 section 6.31

Issue Links

• depends upon: HIVE-14412 Add timestamp with time zone