-----------------------------------------------------------
This is an automatically generated e-mail. To reply, visit:
https://reviews.apache.org/r/70841/
-----------------------------------------------------------
Review request for hive.
Bugs: HIVE-21576
https://issues.apache.org/jira/browse/HIVE-21576
Repository: hive-git
Description
-------
Timestamp and date handling and formatting are currently implemented in Hive
using (sometimes very specific) Java SimpleDateFormat patterns with both
SimpleDateFormat and java.time.DateTimeFormatter, however, these patterns are
not what most standard SQL systems use. For example see Vertica, Netezza,
Oracle, and PostgreSQL.
**Cast...Format**
SQL:2016 introduced the FORMAT clause for CAST which is the standard way to do
string <-> datetime conversions
For example:
CAST(<datetime> AS <char string type> [FORMAT <template>])
CAST(<char string> AS <datetime type> [FORMAT <template>])
cast(dt as string format 'DD-MM-YYYY')
cast('01-05-2017' as date format 'DD-MM-YYYY')
Stuff like this wouldn't need to happen.
**New SQL:2016 Patterns**
Some conflicting examples:
SimpleDateTime: 'MMM dd, yyyy HH:mm:ss'
SQL:2016: 'mon dd, yyyy hh24:mi:ss'
SimpleDateTime: 'yyyy-MM-dd HH:mm:ss'
SQL:2016: 'yyyy-mm-dd hh24:mi:ss'
For the full list of patterns, see subsection "Proposal for Impala’s datetime
patterns" in this doc:
https://docs.google.com/document/d/1V7k6-lrPGW7_uhqM-FhKl3QsxwCRy69v2KIxPsGjc1k/edit
**Continued usage of SimpleDateFormat patterns**
[Update] This feature will NOT be behind a flag in order to keep things simple
for users. Existing Hive functions that accept SimpleDateFormat patterns as
input will continue to do so. Please let me know if you disagree with this
decision. These are the functions (afaik) affected:
from_unixtime(bigint unixtime[, string format])
unix_timestamp(string date, string pattern)
to_unix_timestamp(date[, pattern])
add_months(string start_date, int num_months, output_date_format)
date_format(date/timestamp/string ts, string fmt)
This description is a heavily edited description of IMPALA-4018.
Diffs
-----
common/src/java/org/apache/hadoop/hive/common/format/datetime/HiveDateTimeFormatter.java
PRE-CREATION
common/src/java/org/apache/hadoop/hive/common/format/datetime/HiveJavaDateTimeFormatter.java
PRE-CREATION
common/src/java/org/apache/hadoop/hive/common/format/datetime/HiveSimpleDateFormatter.java
PRE-CREATION
common/src/java/org/apache/hadoop/hive/common/format/datetime/HiveSqlDateTimeFormatter.java
PRE-CREATION
common/src/java/org/apache/hadoop/hive/common/format/datetime/package-info.java
PRE-CREATION
common/src/java/org/apache/hadoop/hive/common/type/Date.java 6ecfcf65c9
common/src/java/org/apache/hadoop/hive/common/type/Timestamp.java a8b7b6d186
common/src/java/org/apache/hadoop/hive/common/type/TimestampUtils.java
f26f8ae01e
common/src/java/org/apache/hive/common/util/DateParser.java 5db14f1906
common/src/test/org/apache/hadoop/hive/common/format/datetime/TestHiveJavaDateTimeFormatter.java
PRE-CREATION
common/src/test/org/apache/hadoop/hive/common/format/datetime/TestHiveSimpleDateFormatter.java
PRE-CREATION
common/src/test/org/apache/hadoop/hive/common/format/datetime/TestHiveSqlDateTimeFormatter.java
PRE-CREATION
common/src/test/org/apache/hadoop/hive/common/format/datetime/package-info.java
PRE-CREATION
ql/src/java/org/apache/hadoop/hive/ql/exec/FunctionRegistry.java d08b05fb68
ql/src/java/org/apache/hadoop/hive/ql/exec/vector/VectorizationContext.java
fa9d1e9783
ql/src/java/org/apache/hadoop/hive/ql/exec/vector/expressions/CastDateToCharWithFormat.java
PRE-CREATION
ql/src/java/org/apache/hadoop/hive/ql/exec/vector/expressions/CastDateToString.java
dfa9f8a00d
ql/src/java/org/apache/hadoop/hive/ql/exec/vector/expressions/CastDateToStringWithFormat.java
PRE-CREATION
ql/src/java/org/apache/hadoop/hive/ql/exec/vector/expressions/CastDateToVarCharWithFormat.java
PRE-CREATION
ql/src/java/org/apache/hadoop/hive/ql/exec/vector/expressions/CastStringToDate.java
a6dff12e1a
ql/src/java/org/apache/hadoop/hive/ql/exec/vector/expressions/CastStringToDateWithFormat.java
PRE-CREATION
ql/src/java/org/apache/hadoop/hive/ql/exec/vector/expressions/CastStringToTimestamp.java
b48b0136eb
ql/src/java/org/apache/hadoop/hive/ql/exec/vector/expressions/CastStringToTimestampWithFormat.java
PRE-CREATION
ql/src/java/org/apache/hadoop/hive/ql/exec/vector/expressions/CastTimestampToCharWithFormat.java
PRE-CREATION
ql/src/java/org/apache/hadoop/hive/ql/exec/vector/expressions/CastTimestampToString.java
adc3a9d7b9
ql/src/java/org/apache/hadoop/hive/ql/exec/vector/expressions/CastTimestampToStringWithFormat.java
PRE-CREATION
ql/src/java/org/apache/hadoop/hive/ql/exec/vector/expressions/CastTimestampToVarCharWithFormat.java
PRE-CREATION
ql/src/java/org/apache/hadoop/hive/ql/parse/IdentifiersParser.g 58fe0cd32e
ql/src/java/org/apache/hadoop/hive/ql/udf/generic/GenericUDFCastFormat.java
PRE-CREATION
ql/src/java/org/apache/hadoop/hive/ql/udf/generic/GenericUDFDateSub.java
bcc4114099
ql/src/test/org/apache/hadoop/hive/ql/exec/vector/expressions/TestVectorMathFunctions.java
663237739e
ql/src/test/org/apache/hadoop/hive/ql/exec/vector/expressions/TestVectorTypeCasts.java
58fd7b030e
ql/src/test/org/apache/hadoop/hive/ql/exec/vector/expressions/TestVectorTypeCastsWithFormat.java
PRE-CREATION
ql/src/test/org/apache/hadoop/hive/ql/udf/generic/TestGenericUDFCastWithFormat.java
PRE-CREATION
ql/src/test/queries/clientpositive/cast_datetime_with_sql_2016_format.q
PRE-CREATION
ql/src/test/results/clientpositive/cast_datetime_with_sql_2016_format.q.out
PRE-CREATION
ql/src/test/results/clientpositive/show_functions.q.out 374e9c4fce
serde/src/java/org/apache/hadoop/hive/serde2/io/DateWritableV2.java
4b6a3d6c10
serde/src/java/org/apache/hadoop/hive/serde2/io/TimestampWritableV2.java
9aa7f19ab2
serde/src/java/org/apache/hadoop/hive/serde2/objectinspector/ObjectInspectorConverters.java
9129177375
serde/src/java/org/apache/hadoop/hive/serde2/objectinspector/primitive/PrimitiveObjectInspectorConverter.java
84c027d51c
serde/src/java/org/apache/hadoop/hive/serde2/objectinspector/primitive/PrimitiveObjectInspectorUtils.java
3886b202c7
Diff: https://reviews.apache.org/r/70841/diff/1/
Testing
-------
Thanks,
Karen Coppage
