> On Aug. 10, 2012, midnight, Carl Steinbach wrote: > > bq. 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. > > > > It's possible for Hive to support the same input parameter order as > > SQLServer and Firebird without breaking backward compatibility. If there > > are two input parameters then it assumes both parameters are date strings. > > If there are three input parameters it assumes that the first parameter is > > the datepart and that the second and third parameters are the date strings. > > > > Also, this patch treats the datepart as a string, but most implementations > > seem to recognize it as a bareword or token. I think the latter approach is > > probably better since it forces the datepart to be a constant which makes > > it possible to generate better optimized code. > >
SQL Server Datediff docs: http://msdn.microsoft.com/en-us/library/aa258269(v=sql.80).aspx Firebird SQL: http://www.firebirdsql.org/refdocs/langrefupd21-intfunc-datediff.html - Carl ----------------------------------------------------------- This is an automatically generated e-mail. To reply, visit: https://reviews.apache.org/r/6027/#review10098 ----------------------------------------------------------- On July 18, 2012, 12:56 a.m., Shefali Vohra wrote: > > ----------------------------------------------------------- > This is an automatically generated e-mail. To reply, visit: > https://reviews.apache.org/r/6027/ > ----------------------------------------------------------- > > (Updated July 18, 2012, 12:56 a.m.) > > > Review request for hive and Ashutosh Chauhan. > > > Description > ------- > > 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 addresses bug HIVE-3216. > https://issues.apache.org/jira/browse/HIVE-3216 > > > Diffs > ----- > > trunk/data/files/datetable.txt PRE-CREATION > trunk/data/files/timestamptable.txt PRE-CREATION > trunk/ql/src/java/org/apache/hadoop/hive/ql/udf/UDFDateDiff.java 1362724 > trunk/ql/src/test/queries/clientnegative/udf_datediff.q PRE-CREATION > trunk/ql/src/test/queries/clientpositive/udf_datediff.q 1362724 > trunk/ql/src/test/results/clientnegative/udf_datediff.q.out PRE-CREATION > trunk/ql/src/test/results/clientpositive/udf_datediff.q.out 1362724 > > Diff: https://reviews.apache.org/r/6027/diff/ > > > Testing > ------- > > positive and negative test cases included > > > Thanks, > > Shefali Vohra > >
