You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@hive.apache.org by "Shyam Sunder Rai (JIRA)" <ji...@apache.org> on 2016/12/05 23:34:59 UTC
[jira] [Comment Edited] (HIVE-11812) datediff sometimes returns
incorrect results when called with dates
[ https://issues.apache.org/jira/browse/HIVE-11812?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15723733#comment-15723733 ]
Shyam Sunder Rai edited comment on HIVE-11812 at 12/5/16 11:34 PM:
-------------------------------------------------------------------
To all the Gurus here, I made a change locally to my code and the datediff function with the test cases given above (in PST) seems to be working correctly for me with all the anticipated results. Can someone help me out on how we are testing it with different timezones ? I performed the test in the following way and am getting the results as expected after my changes.
{code}
SELECT datediff(TO_DATE(FROM_UTC_TIMESTAMP(UNIX_TIMESTAMP(t.c1, "yyyy-MM-dd") * 1000, 'EST')), TO_DATE(FROM_UTC_TIMESTAMP(UNIX_TIMESTAMP('2015-09-14', "yyyy-MM-dd") * 1000, 'EST'))) FROM t;
SELECT datediff(TO_DATE(FROM_UTC_TIMESTAMP(UNIX_TIMESTAMP(t.c1, "yyyy-MM-dd") * 1000, 'EST')), TO_DATE(FROM_UTC_TIMESTAMP(UNIX_TIMESTAMP('2015-09-15', "yyyy-MM-dd") * 1000, 'EST'))) FROM t;
SELECT datediff(TO_DATE(FROM_UTC_TIMESTAMP(UNIX_TIMESTAMP(t.c1, "yyyy-MM-dd") * 1000, 'EST')), TO_DATE(FROM_UTC_TIMESTAMP(UNIX_TIMESTAMP('2015-09-16', "yyyy-MM-dd") * 1000, 'EST'))) FROM t;
SELECT datediff(TO_DATE(FROM_UTC_TIMESTAMP(UNIX_TIMESTAMP('2015-09-14', "yyyy-MM-dd") * 1000, 'EST')), TO_DATE(FROM_UTC_TIMESTAMP(UNIX_TIMESTAMP(t.c1, "yyyy-MM-dd") * 1000, 'EST'))) FROM t;
SELECT datediff(TO_DATE(FROM_UTC_TIMESTAMP(UNIX_TIMESTAMP('2015-09-15', "yyyy-MM-dd") * 1000, 'EST')), TO_DATE(FROM_UTC_TIMESTAMP(UNIX_TIMESTAMP(t.c1, "yyyy-MM-dd") * 1000, 'EST'))) FROM t;
SELECT datediff(TO_DATE(FROM_UTC_TIMESTAMP(UNIX_TIMESTAMP('2015-09-16', "yyyy-MM-dd") * 1000, 'EST')), TO_DATE(FROM_UTC_TIMESTAMP(UNIX_TIMESTAMP(t.c1, "yyyy-MM-dd") * 1000, 'EST'))) FROM t;
SELECT datediff(TO_DATE(FROM_UTC_TIMESTAMP(UNIX_TIMESTAMP(t.c2, "yyyy-MM-dd") * 1000, 'EST')), TO_DATE(FROM_UTC_TIMESTAMP(UNIX_TIMESTAMP('2015-09-14', "yyyy-MM-dd") * 1000, 'EST'))) FROM t;
SELECT datediff(TO_DATE(FROM_UTC_TIMESTAMP(UNIX_TIMESTAMP(t.c2, "yyyy-MM-dd") * 1000, 'EST')), TO_DATE(FROM_UTC_TIMESTAMP(UNIX_TIMESTAMP('2015-09-15', "yyyy-MM-dd") * 1000, 'EST'))) FROM t;
SELECT datediff(TO_DATE(FROM_UTC_TIMESTAMP(UNIX_TIMESTAMP(t.c2, "yyyy-MM-dd") * 1000, 'EST')), TO_DATE(FROM_UTC_TIMESTAMP(UNIX_TIMESTAMP('2015-09-16', "yyyy-MM-dd") * 1000, 'EST'))) FROM t;
SELECT datediff(TO_DATE(FROM_UTC_TIMESTAMP(UNIX_TIMESTAMP('2015-09-14', "yyyy-MM-dd") * 1000, 'EST')), TO_DATE(FROM_UTC_TIMESTAMP(UNIX_TIMESTAMP(t.c2, "yyyy-MM-dd") * 1000, 'EST'))) FROM t;
SELECT datediff(TO_DATE(FROM_UTC_TIMESTAMP(UNIX_TIMESTAMP('2015-09-15', "yyyy-MM-dd") * 1000, 'EST')), TO_DATE(FROM_UTC_TIMESTAMP(UNIX_TIMESTAMP(t.c2, "yyyy-MM-dd") * 1000, 'EST'))) FROM t;
SELECT datediff(TO_DATE(FROM_UTC_TIMESTAMP(UNIX_TIMESTAMP('2015-09-16', "yyyy-MM-dd") * 1000, 'EST')), TO_DATE(FROM_UTC_TIMESTAMP(UNIX_TIMESTAMP(t.c2, "yyyy-MM-dd") * 1000, 'EST'))) FROM t;
{code}
Tried these changes with multiple timezones to try variations, including IST. The server itself is on PST.
was (Author: shyamsunderrai):
To all the Gurus here, I made a change locally to my code and the datediff function with the test cases given above (in PST) seems to be working correctly for me with all the anticipated results. Can someone help me out on how we are testing it with different timezones ? I performed the test in the following way and am getting the results as expected after my changes.
{code}
SELECT datediff(TO_DATE(FROM_UTC_TIMESTAMP(UNIX_TIMESTAMP(t.c1, "yyyy-MM-dd") * 1000, 'EST')), TO_DATE(FROM_UTC_TIMESTAMP(UNIX_TIMESTAMP('2015-09-14', "yyyy-MM-dd") * 1000, 'EST'))) FROM t;
SELECT datediff(TO_DATE(FROM_UTC_TIMESTAMP(UNIX_TIMESTAMP(t.c1, "yyyy-MM-dd") * 1000, 'EST')), TO_DATE(FROM_UTC_TIMESTAMP(UNIX_TIMESTAMP('2015-09-15', "yyyy-MM-dd") * 1000, 'EST'))) FROM t;
SELECT datediff(TO_DATE(FROM_UTC_TIMESTAMP(UNIX_TIMESTAMP(t.c1, "yyyy-MM-dd") * 1000, 'EST')), TO_DATE(FROM_UTC_TIMESTAMP(UNIX_TIMESTAMP('2015-09-16', "yyyy-MM-dd") * 1000, 'EST'))) FROM t;
SELECT datediff(TO_DATE(FROM_UTC_TIMESTAMP(UNIX_TIMESTAMP('2015-09-14', "yyyy-MM-dd") * 1000, 'EST')), TO_DATE(FROM_UTC_TIMESTAMP(UNIX_TIMESTAMP(t.c1, "yyyy-MM-dd") * 1000, 'EST'))) FROM t;
SELECT datediff(TO_DATE(FROM_UTC_TIMESTAMP(UNIX_TIMESTAMP('2015-09-15', "yyyy-MM-dd") * 1000, 'EST')), TO_DATE(FROM_UTC_TIMESTAMP(UNIX_TIMESTAMP(t.c1, "yyyy-MM-dd") * 1000, 'EST'))) FROM t;
SELECT datediff(TO_DATE(FROM_UTC_TIMESTAMP(UNIX_TIMESTAMP('2015-09-16', "yyyy-MM-dd") * 1000, 'EST')), TO_DATE(FROM_UTC_TIMESTAMP(UNIX_TIMESTAMP(t.c1, "yyyy-MM-dd") * 1000, 'EST'))) FROM t;
SELECT datediff(TO_DATE(FROM_UTC_TIMESTAMP(UNIX_TIMESTAMP(t.c2, "yyyy-MM-dd") * 1000, 'EST')), TO_DATE(FROM_UTC_TIMESTAMP(UNIX_TIMESTAMP('2015-09-14', "yyyy-MM-dd") * 1000, 'EST'))) FROM t;
SELECT datediff(TO_DATE(FROM_UTC_TIMESTAMP(UNIX_TIMESTAMP(t.c2, "yyyy-MM-dd") * 1000, 'EST')), TO_DATE(FROM_UTC_TIMESTAMP(UNIX_TIMESTAMP('2015-09-15', "yyyy-MM-dd") * 1000, 'EST'))) FROM t;
SELECT datediff(TO_DATE(FROM_UTC_TIMESTAMP(UNIX_TIMESTAMP(t.c2, "yyyy-MM-dd") * 1000, 'EST')), TO_DATE(FROM_UTC_TIMESTAMP(UNIX_TIMESTAMP('2015-09-16', "yyyy-MM-dd") * 1000, 'EST'))) FROM t;
SELECT datediff(TO_DATE(FROM_UTC_TIMESTAMP(UNIX_TIMESTAMP('2015-09-14', "yyyy-MM-dd") * 1000, 'EST')), TO_DATE(FROM_UTC_TIMESTAMP(UNIX_TIMESTAMP(t.c2, "yyyy-MM-dd") * 1000, 'EST'))) FROM t;
SELECT datediff(TO_DATE(FROM_UTC_TIMESTAMP(UNIX_TIMESTAMP('2015-09-15', "yyyy-MM-dd") * 1000, 'EST')), TO_DATE(FROM_UTC_TIMESTAMP(UNIX_TIMESTAMP(t.c2, "yyyy-MM-dd") * 1000, 'EST'))) FROM t;
SELECT datediff(TO_DATE(FROM_UTC_TIMESTAMP(UNIX_TIMESTAMP('2015-09-16', "yyyy-MM-dd") * 1000, 'EST')), TO_DATE(FROM_UTC_TIMESTAMP(UNIX_TIMESTAMP(t.c2, "yyyy-MM-dd") * 1000, 'EST'))) FROM t;
{code}
> datediff sometimes returns incorrect results when called with dates
> -------------------------------------------------------------------
>
> Key: HIVE-11812
> URL: https://issues.apache.org/jira/browse/HIVE-11812
> Project: Hive
> Issue Type: Bug
> Components: UDF
> Affects Versions: 2.0.0
> Reporter: Nicholas Brenwald
> Assignee: Chetna Chaudhari
> Priority: Minor
> Attachments: HIVE-11812.1.patch
>
>
> DATEDIFF returns an incorrect result when one of the arguments is a date type.
> The Hive Language Manual provides the following signature for datediff:
> {code}
> int datediff(string enddate, string startdate)
> {code}
> I think datediff should either throw an error (if date types are not supported), or return the correct result.
> To reproduce, create a table:
> {code}
> create table t (c1 string, c2 date);
> {code}
> Assuming you have a table x containing some data, populate table t with 1 row:
> {code}
> insert into t select '2015-09-15', '2015-09-15' from x limit 1;
> {code}
> Then run the following 12 test queries:
> {code}
> select datediff(c1, '2015-09-14') from t;
> select datediff(c1, '2015-09-15') from t;
> select datediff(c1, '2015-09-16') from t;
> select datediff('2015-09-14', c1) from t;
> select datediff('2015-09-15', c1) from t;
> select datediff('2015-09-16', c1) from t;
> select datediff(c2, '2015-09-14') from t;
> select datediff(c2, '2015-09-15') from t;
> select datediff(c2, '2015-09-16') from t;
> select datediff('2015-09-14', c2) from t;
> select datediff('2015-09-15', c2) from t;
> select datediff('2015-09-16', c2) from t;
> {code}
> The below table summarises the result. All results for column c1 (which is a string) are correct, but when using c2 (which is a date), two of the results are incorrect.
> || Test || Expected Result || Actual Result || Passed / Failed ||
> |datediff(c1, '2015-09-14')| 1 | 1| Passed |
> |datediff(c1, '2015-09-15')| 0 | 0| Passed |
> |datediff(c1, '2015-09-16') | -1 | -1| Passed |
> |datediff('2015-09-14', c1) | -1 | -1| Passed |
> |datediff('2015-09-15', c1)| 0 | 0| Passed |
> |datediff('2015-09-16', c1)| 1 | 1| Passed |
> |datediff(c2, '2015-09-14')| 1 | 0| {color:red}Failed{color} |
> |datediff(c2, '2015-09-15')| 0 | 0| Passed |
> |datediff(c2, '2015-09-16') | -1 | -1| Passed |
> |datediff('2015-09-14', c2) | -1 | 0 | {color:red}Failed{color} |
> |datediff('2015-09-15', c2)| 0 | 0| Passed |
> |datediff('2015-09-16', c2)| 1 | 1| Passed |
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)