[
https://issues.apache.org/jira/browse/HIVE-3216?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Stamatis Zampetakis updated HIVE-3216:
--------------------------------------
I cleared the fixVersion field since this ticket is not resolved. Please review
this ticket and if the fix is already committed to a specific version please
set the version accordingly and mark the ticket as RESOLVED.
According to the JIRA guidelines
(https://cwiki.apache.org/confluence/display/Hive/HowToContribute) the
fixVersion should be set only when the issue is resolved/closed.
> 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.9.1, 0.10.0
> Reporter: Shefali Vohra
> Assignee: 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 was sent by Atlassian Jira
(v8.20.10#820010)
