You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@hive.apache.org by "Shefali Vohra (JIRA)" <ji...@apache.org> on 2012/06/30 01:12:42 UTC
[jira] [Created] (HIVE-3216) DateDiff UDF overload with a unit
parameter
Shefali Vohra created HIVE-3216:
-----------------------------------
Summary: 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
Fix For: 0.9.1
*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
[jira] [Assigned] (HIVE-3216) DateDiff UDF overload with a unit
parameter
Posted by "Ashutosh Chauhan (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/HIVE-3216?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Ashutosh Chauhan reassigned HIVE-3216:
--------------------------------------
Assignee: Shefali Vohra
> 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
> 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 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
[jira] [Updated] (HIVE-3216) DateDiff UDF overload with a unit
parameter
Posted by "Shefali Vohra (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/HIVE-3216?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Shefali Vohra updated HIVE-3216:
--------------------------------
Attachment: HIVE-3216.1.patch.txt
Review Link: https://reviews.apache.org/r/6027/
> 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
[jira] [Updated] (HIVE-3216) DateDiff UDF overload with a unit
parameter
Posted by "Shefali Vohra (JIRA)" <ji...@apache.org>.
[ 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
[jira] [Commented] (HIVE-3216) DateDiff UDF overload with a unit
parameter
Posted by "Ashutosh Chauhan (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/HIVE-3216?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13429633#comment-13429633 ]
Ashutosh Chauhan commented on HIVE-3216:
----------------------------------------
[~shefaliv] I left some review comments on Review Board. By the way, good work in providing extensive test coverage.
> 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
> 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 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
[jira] [Commented] (HIVE-3216) DateDiff UDF overload with a unit
parameter
Posted by "Shefali Vohra (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/HIVE-3216?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13432176#comment-13432176 ]
Shefali Vohra commented on HIVE-3216:
-------------------------------------
Ashutosh, I have left some comments on the review board. Can you please take a look when you get a chance?
> 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
> 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 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
[jira] [Commented] (HIVE-3216) DateDiff UDF overload with a unit
parameter
Posted by "N Campbell (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/HIVE-3216?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13407002#comment-13407002 ]
N Campbell commented on HIVE-3216:
----------------------------------
Note, ISO-SQL does not have DateDiff rather <timestamp> - <timestamp> returns an interval type where you can say ( ... ) interval month etc to control what interval type you want. The DateDiff like function if semantically equivalent would be a syntactic short form of the equivalent intent however the return type would be a numeric type and not an interval.
> 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
>
>
> *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