[
https://issues.apache.org/jira/browse/HIVE-3216?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Shefali Vohra updated HIVE-3216:
--------------------------------
Status: Patch Available (was: Open)
> DateDiff UDF overload with a unit parameter
> -------------------------------------------
>
> Key: HIVE-3216
> URL: https://issues.apache.org/jira/browse/HIVE-3216
> Project: Hive
> Issue Type: Improvement
> Components: SQL, UDF
> Affects Versions: 0.10.0, 0.9.1
> Reporter: Shefali Vohra
> Priority: Minor
> Labels: date, patch, sql, timestamp, udf
> Fix For: 0.9.1
>
> Attachments: HIVE-3216.1.patch.txt
>
>
> *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 message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators:
https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
