hive-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Shefali Vohra (JIRA)" <>
Subject [jira] [Updated] (HIVE-3216) DateDiff UDF overload with a unit parameter
Date Wed, 18 Jul 2012 01:01:53 GMT


Shefali Vohra updated HIVE-3216:

    Status: Patch Available  (was: Open)
> DateDiff UDF overload with a unit parameter
> -------------------------------------------
>                 Key: HIVE-3216
>                 URL:
>             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
> 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
> 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:!default.jspa
For more information on JIRA, see:


View raw message