[HIVE-20007] Hive should carry out timestamp computations in UTC

• Type: Sub-task
• Status: Closed
• Priority: Blocker
• Resolution: Fixed
• Fix Version/s: 4.0.0-alpha-1
• Component/s: Hive
• Labels: timestamp
• Target Version/s: 4.0.0
• Docs Text: https://cwiki.apache.org/confluence/display/Hive/Different+TIMESTAMP+types
• Assignee: Jesús Camacho Rodríguez
• Reporter: Ryan Blue
• Created: 27/Jun/18 07:36
• Updated: 07/Jun/24 10:28
• Resolved: 28/Jun/18 15:45

Description

Hive currently uses the "local" time of a java.sql.Timestamp to represent the SQL data type TIMESTAMP WITHOUT TIME ZONE. The purpose is to be able to use Timestamp#getYear() and similar methods to implement SQL functions like year.

When the SQL session's time zone is a DST zone, such as America/Los_Angeles that alternates between PST and PDT, there are times that cannot be represented because the effective zone skips them.

hive> select TIMESTAMP '2015-03-08 02:10:00.101';
2015-03-08 03:10:00.101

Using UTC instead of the SQL session time zone as the underlying zone for a java.sql.Timestamp avoids this bug, while still returning correct values for getYear etc. Using UTC as the convenience representation (timestamp without time zone has no real zone) would make timestamp calculations more consistent and avoid similar problems in the future.

Notably, this would break the unix_timestamp UDF that specifies the result is with respect to "the default timezone and default locale". That function would need to be updated to use the System.getProperty("user.timezone") zone.

Issue Links

• blocks: HIVE-18729 Druid Time column type
• causes: HIVE-25104 Backward incompatible timestamp serialization in Parquet for certain timezones
• causes: HIVE-25268 date_format udf returns wrong results for dates prior to 1900 if the local timezone is other than UTC
• causes: HIVE-26233 Problems reading back PARQUET timestamps above 10000 years
• causes: HIVE-20135 Fix incompatible change in TimestampColumnVector to default to UTC
• incorporates: HIVE-19443 Issue with Druid timestamp with timezone handling
• is a clone of: HIVE-12192 Hive should carry out timestamp computations in UTC
• is related to: HIVE-20569 hour/minute/second UDFs not working on String values with Time format
• is related to: HIVE-25129 Wrong results when timestamps stored in Avro/Parquet fall into the DST shift
• is related to: HIVE-28311 Backward compatibility of java.sql.Date and java.sql.Timestamp in hive-serde
• is related to: HIVE-19226 Extend storage-api to print timestamp values in UTC
• is related to: HIVE-16614 Support "set local time zone" statement
• is related to: HIVE-27586 Parse dates from strings ignoring trailing (potentialy) invalid chars
• is related to: ORC-341 Add ability to use timestamp in UTC rather than local timezone.
• links to: RB link