-----------------------------------------------------------
This is an automatically generated e-mail. To reply, visit:
https://reviews.apache.org/r/6027/
-----------------------------------------------------------
Review request for hive and Ashutosh Chauhan.
Description
-------
Parameters
This function overloads the current DateDiff(expr1, expr2) by adding another
parameter to specify the units. It takes 3 parameters. The first two are
timestamps, and the formats accepted are:
yyyy-MM-dd
yyyy-MM-dd HH:mm:ss
yyyy-MM-dd HH:mm:ss.milli
These are the formats accepted by the current DateDiff(expr1, expr2) function
and allow for that consistency. The accepted data types for the timestamp will
be Text, TimestampWritable, Date, and String, just as with the already existing
function.
The third parameter is the units the user wants the response to be in.
Acceptable units are:
Microsecond
Millisecond
Second
Minute
Hour
Day
Week
Month
Quarter
Year
When calculating the difference, the full timestamp is used when the specified
unit is hour or smaller (microsecond, millisecond, second, minute, hour), and
only the date part is used if the unit is day or larger (day, week, month,
quarter, year). If for the smaller units the time is not specified and the
format yyyy-MM-dd is used, the time 00:00:00.0 is used. Leap years are
accounted for by the Calendar class in Java, which inherently addresses the
issue.
The assumption is made that all these time parameters are in the same time zone.
Return Value
The function returns expr1 - expr2 expressed as an int in the units specified.
Hive vs. SQL
SQL also has a DateDiff() function with some more acceptable units. The order
of parameters is different between SQL and Hive. The reason for this is that
Hive already has a DateDiff() function with the same first two parameters, and
having this order here allows for that consistency within Hive.
Example Query
hive > DATEDIFF(DATE_FIELD, '2012-06-01', ‘day’);
Diagnostic Error Messages
Invalid table alias or column name reference
Table not found
This addresses bug HIVE-3216.
https://issues.apache.org/jira/browse/HIVE-3216
Diffs
-----
trunk/data/files/datetable.txt PRE-CREATION
trunk/data/files/timestamptable.txt PRE-CREATION
trunk/ql/src/java/org/apache/hadoop/hive/ql/udf/UDFDateDiff.java 1362724
trunk/ql/src/test/queries/clientnegative/udf_datediff.q PRE-CREATION
trunk/ql/src/test/queries/clientpositive/udf_datediff.q 1362724
trunk/ql/src/test/results/clientnegative/udf_datediff.q.out PRE-CREATION
trunk/ql/src/test/results/clientpositive/udf_datediff.q.out 1362724
Diff: https://reviews.apache.org/r/6027/diff/
Testing
-------
yes
Thanks,
Shefali Vohra
