Index: data/files/timestamptable.txt =================================================================== --- data/files/timestamptable.txt (revision 0) +++ data/files/timestamptable.txt (revision 0) @@ -0,0 +1,7 @@ +2012-03-09 05:23:01, 2012-12-05 09:08:00.096 +2012-06-23 05:06:09, 2012-12-05 05:16:09 +2012-12-19 08:19:19, 2012-12-15 08:10:19 +2012-07-24 08:00:00, 2012-12-05 09:51:00 +2012-02-01 07:07:07, 1991-02-01 07:07:07 +2015-06-10 23:59:00, 1991-09-10 23:59:00.7 +1991-07-03 05:23:29, 1991-07-03 05:23:29.356 \ No newline at end of file Index: data/files/datetable.txt =================================================================== --- data/files/datetable.txt (revision 0) +++ data/files/datetable.txt (revision 0) @@ -0,0 +1,8 @@ +2012-12-15, 2012-12-05 +2005-07-05 05:06:09, 2012-12-05 05:16:09 +1987-01-22 08:19:19, 2012-12-15 08:10:19 +2010-02-14, 2012-12-05 +2012-05-16, 1991-02-1 +2015-09-10, 1991-09-10 11:59:00.7 +2002-08-24, 1991-07-03 05:23:29.356 +2012-02-29, 2010-02-28 \ No newline at end of file Index: ql/src/test/results/clientnegative/udf_datediff.q.out =================================================================== --- ql/src/test/results/clientnegative/udf_datediff.q.out (revision 0) +++ ql/src/test/results/clientnegative/udf_datediff.q.out (revision 0) @@ -0,0 +1,76 @@ +PREHOOK: query: DESCRIBE FUNCTION datediff +PREHOOK: type: DESCFUNCTION +POSTHOOK: query: DESCRIBE FUNCTION datediff +POSTHOOK: type: DESCFUNCTION +datediff(date1, date2) - Returns the number of days between date1 and date2 +datediff(date1, date2, unit) - Returns the difference between date1 and date2 in the units specified. +PREHOOK: query: DESCRIBE FUNCTION EXTENDED datediff +PREHOOK: type: DESCFUNCTION +POSTHOOK: query: DESCRIBE FUNCTION EXTENDED datediff +POSTHOOK: type: DESCFUNCTION +datediff(date1, date2) - Returns the number of days between date1 and date2 +datediff(date1, date2, unit) - Returns the difference between date1 and date2 in the units specified. +date1 and date2 are strings/text or timestamps in the format and unit is a string/text'yyyy-MM-dd HH:mm:ss', 'yyyy-MM-dd', 'yyyy-MM-dd HH:mm:ss.milli', or 'yyyy-MM-dd'.If date1 is earlier than date2, the result is negative. +Example: + > SELECT datediff('2009-30-07', '2009-31-07', 'day') FROM src; + -1 +PREHOOK: query: create table datetable (date1 string, date2 string) row format delimited fields terminated by ', ' +PREHOOK: type: CREATETABLE +POSTHOOK: query: create table datetable (date1 string, date2 string) row format delimited fields terminated by ', ' +POSTHOOK: type: CREATETABLE +POSTHOOK: Output: default@datetable +PREHOOK: query: load data local inpath '../data/files/datetable.txt' overwrite into table datetable +PREHOOK: type: LOAD +PREHOOK: Output: default@datetable +POSTHOOK: query: load data local inpath '../data/files/datetable.txt' overwrite into table datetable +POSTHOOK: type: LOAD +POSTHOOK: Output: default@datetable +PREHOOK: query: select * from datetable +PREHOOK: type: QUERY +PREHOOK: Input: default@datetable +#### A masked pattern was here #### +POSTHOOK: query: select * from datetable +POSTHOOK: type: QUERY +POSTHOOK: Input: default@datetable +#### A masked pattern was here #### +2012-12-15 2012-12-05 +2005-07-05 05:06:09 2012-12-05 05:16:09 +1987-01-22 08:19:19 2012-12-15 08:10:19 +2010-02-14 2012-12-05 +2012-05-16 1991-02-1 +2015-09-10 1991-09-10 11:59:00.7 +2002-08-24 1991-07-03 05:23:29.356 +2012-02-29 2010-02-28 +PREHOOK: query: select datediff(date1, date2, 'foo') from datetable +PREHOOK: type: QUERY +PREHOOK: Input: default@datetable +#### A masked pattern was here #### +POSTHOOK: query: select datediff(date1, date2, 'foo') from datetable +POSTHOOK: type: QUERY +POSTHOOK: Input: default@datetable +#### A masked pattern was here #### +10 +-2710 +-9459 +-1025 +7775 +8766 +4070 +731 +PREHOOK: query: select datediff(date1, 'foo') from datetable +PREHOOK: type: QUERY +PREHOOK: Input: default@datetable +#### A masked pattern was here #### +POSTHOOK: query: select datediff(date1, 'foo') from datetable +POSTHOOK: type: QUERY +POSTHOOK: Input: default@datetable +#### A masked pattern was here #### +NULL +NULL +NULL +NULL +NULL +NULL +NULL +NULL +FAILED: SemanticException [Error 10014]: Line 2:7 Wrong arguments 'datediff': No matching method for class org.apache.hadoop.hive.ql.udf.UDFDateDiff with (). Possible choices: _FUNC_(string, string) _FUNC_(timestamp, timestamp) _FUNC_(timestamp, string) _FUNC_(string, timestamp) _FUNC_(string, string, string) _FUNC_(timestamp, timestamp, string) _FUNC_(timestamp, string, string) _FUNC_(string, timestamp, string) Index: ql/src/test/results/clientpositive/udf_datediff.q.out =================================================================== --- ql/src/test/results/clientpositive/udf_datediff.q.out (revision 1362724) +++ ql/src/test/results/clientpositive/udf_datediff.q.out (working copy) @@ -3,12 +3,445 @@ POSTHOOK: query: DESCRIBE FUNCTION datediff POSTHOOK: type: DESCFUNCTION datediff(date1, date2) - Returns the number of days between date1 and date2 +datediff(date1, date2, unit) - Returns the difference between date1 and date2 in the units specified. PREHOOK: query: DESCRIBE FUNCTION EXTENDED datediff PREHOOK: type: DESCFUNCTION POSTHOOK: query: DESCRIBE FUNCTION EXTENDED datediff POSTHOOK: type: DESCFUNCTION datediff(date1, date2) - Returns the number of days between date1 and date2 -date1 and date2 are strings in the format 'yyyy-MM-dd HH:mm:ss' or 'yyyy-MM-dd'. The time parts are ignored.If date1 is earlier than date2, the result is negative. +datediff(date1, date2, unit) - Returns the difference between date1 and date2 in the units specified. +date1 and date2 are strings/text or timestamps in the format and unit is a string/text'yyyy-MM-dd HH:mm:ss', 'yyyy-MM-dd', 'yyyy-MM-dd HH:mm:ss.milli', or 'yyyy-MM-dd'.If date1 is earlier than date2, the result is negative. Example: - > SELECT datediff('2009-30-07', '2009-31-07') FROM src LIMIT 1; - 1 + > SELECT datediff('2009-30-07', '2009-31-07', 'day') FROM src; + -1 +PREHOOK: query: create table datetable (date1 string, date2 string) row format delimited fields terminated by ', ' +PREHOOK: type: CREATETABLE +POSTHOOK: query: create table datetable (date1 string, date2 string) row format delimited fields terminated by ', ' +POSTHOOK: type: CREATETABLE +POSTHOOK: Output: default@datetable +PREHOOK: query: load data local inpath '../data/files/datetable.txt' overwrite into table datetable +PREHOOK: type: LOAD +PREHOOK: Output: default@datetable +POSTHOOK: query: load data local inpath '../data/files/datetable.txt' overwrite into table datetable +POSTHOOK: type: LOAD +POSTHOOK: Output: default@datetable +PREHOOK: query: select * from datetable +PREHOOK: type: QUERY +PREHOOK: Input: default@datetable +#### A masked pattern was here #### +POSTHOOK: query: select * from datetable +POSTHOOK: type: QUERY +POSTHOOK: Input: default@datetable +#### A masked pattern was here #### +2012-12-15 2012-12-05 +2005-07-05 05:06:09 2012-12-05 05:16:09 +1987-01-22 08:19:19 2012-12-15 08:10:19 +2010-02-14 2012-12-05 +2012-05-16 1991-02-1 +2015-09-10 1991-09-10 11:59:00.7 +2002-08-24 1991-07-03 05:23:29.356 +2012-02-29 2010-02-28 +PREHOOK: query: select datediff(date1, date2, 'day') from datetable +PREHOOK: type: QUERY +PREHOOK: Input: default@datetable +#### A masked pattern was here #### +POSTHOOK: query: select datediff(date1, date2, 'day') from datetable +POSTHOOK: type: QUERY +POSTHOOK: Input: default@datetable +#### A masked pattern was here #### +10 +-2710 +-9459 +-1025 +7775 +8766 +4070 +731 +PREHOOK: query: select datediff(date1, '2012-06-13', 'hour') from datetable +PREHOOK: type: QUERY +PREHOOK: Input: default@datetable +#### A masked pattern was here #### +POSTHOOK: query: select datediff(date1, '2012-06-13', 'hour') from datetable +POSTHOOK: type: QUERY +POSTHOOK: Input: default@datetable +#### A masked pattern was here #### +4440 +-60834 +-222567 +-20400 +-672 +28416 +-85944 +-2520 +PREHOOK: query: select datediff(date1, '2012-06-13', 'year') from datetable +PREHOOK: type: QUERY +PREHOOK: Input: default@datetable +#### A masked pattern was here #### +POSTHOOK: query: select datediff(date1, '2012-06-13', 'year') from datetable +POSTHOOK: type: QUERY +POSTHOOK: Input: default@datetable +#### A masked pattern was here #### +0 +-6 +-25 +-2 +0 +3 +-9 +0 +PREHOOK: query: select datediff(date1, '2012-06-13', 'quarter') from datetable +PREHOOK: type: QUERY +PREHOOK: Input: default@datetable +#### A masked pattern was here #### +POSTHOOK: query: select datediff(date1, '2012-06-13', 'quarter') from datetable +POSTHOOK: type: QUERY +POSTHOOK: Input: default@datetable +#### A masked pattern was here #### +2 +-27 +-101 +-9 +0 +13 +-39 +-1 +PREHOOK: query: select datediff(date1, '2012-06-13', 'month') from datetable +PREHOOK: type: QUERY +PREHOOK: Input: default@datetable +#### A masked pattern was here #### +POSTHOOK: query: select datediff(date1, '2012-06-13', 'month') from datetable +POSTHOOK: type: QUERY +POSTHOOK: Input: default@datetable +#### A masked pattern was here #### +6 +-83 +-305 +-28 +-1 +39 +-118 +-4 +PREHOOK: query: select datediff(date1, '2012-06-13', 'week') from datetable +PREHOOK: type: QUERY +PREHOOK: Input: default@datetable +#### A masked pattern was here #### +POSTHOOK: query: select datediff(date1, '2012-06-13', 'week') from datetable +POSTHOOK: type: QUERY +POSTHOOK: Input: default@datetable +#### A masked pattern was here #### +26 +-362 +-1324 +-121 +-4 +169 +-511 +-15 +PREHOOK: query: select datediff('2012-06-13', date2, 'minute') from datetable +PREHOOK: type: QUERY +PREHOOK: Input: default@datetable +#### A masked pattern was here #### +POSTHOOK: query: select datediff('2012-06-13', date2, 'minute') from datetable +POSTHOOK: type: QUERY +POSTHOOK: Input: default@datetable +#### A masked pattern was here #### +-252000 +-252316 +-266890 +-252000 +11236320 +10917360 +11017116 +1203840 +PREHOOK: query: select datediff(date1, '2012-06-13 05:06:25', 'minute') from datetable +PREHOOK: type: QUERY +PREHOOK: Input: default@datetable +#### A masked pattern was here #### +POSTHOOK: query: select datediff(date1, '2012-06-13 05:06:25', 'minute') from datetable +POSTHOOK: type: QUERY +POSTHOOK: Input: default@datetable +#### A masked pattern was here #### +266093 +-3650400 +-13354367 +-1224306 +-40626 +1704653 +-5156946 +-151506 +PREHOOK: query: select datediff(date1, '2012-06-13 05:06:25', 'second') from datetable +PREHOOK: type: QUERY +PREHOOK: Input: default@datetable +#### A masked pattern was here #### +POSTHOOK: query: select datediff(date1, '2012-06-13 05:06:25', 'second') from datetable +POSTHOOK: type: QUERY +POSTHOOK: Input: default@datetable +#### A masked pattern was here #### +15965615 +-219024016 +-801262026 +-73458385 +-2437585 +102279215 +-309416785 +-9090385 +PREHOOK: query: select datediff(date1, '2012-06-13 05:06:25', 'millisecond') from datetable +PREHOOK: type: QUERY +PREHOOK: Input: default@datetable +#### A masked pattern was here #### +POSTHOOK: query: select datediff(date1, '2012-06-13 05:06:25', 'millisecond') from datetable +POSTHOOK: type: QUERY +POSTHOOK: Input: default@datetable +#### A masked pattern was here #### +15965615000 +-219024016000 +-801262026000 +-73458385000 +-2437585000 +102279215000 +-309416785000 +-9090385000 +PREHOOK: query: select datediff(date1, '2012-06-13 05:06:25.9', 'millisecond') from datetable +PREHOOK: type: QUERY +PREHOOK: Input: default@datetable +#### A masked pattern was here #### +POSTHOOK: query: select datediff(date1, '2012-06-13 05:06:25.9', 'millisecond') from datetable +POSTHOOK: type: QUERY +POSTHOOK: Input: default@datetable +#### A masked pattern was here #### +15965614991 +-219024016009 +-801262026009 +-73458385009 +-2437585009 +102279214991 +-309416785009 +-9090385009 +PREHOOK: query: select datediff(date1, '2012-06-13 05:06:25.9', 'microsecond') from datetable +PREHOOK: type: QUERY +PREHOOK: Input: default@datetable +#### A masked pattern was here #### +POSTHOOK: query: select datediff(date1, '2012-06-13 05:06:25.9', 'microsecond') from datetable +POSTHOOK: type: QUERY +POSTHOOK: Input: default@datetable +#### A masked pattern was here #### +15965614991000 +-219024016009000 +-801262026009000 +-73458385009000 +-2437585009000 +102279214991000 +-309416785009000 +-9090385009000 +PREHOOK: query: select datediff(date2, '2012-06-13 05:06:25.9', 'quarter') from datetable +PREHOOK: type: QUERY +PREHOOK: Input: default@datetable +#### A masked pattern was here #### +POSTHOOK: query: select datediff(date2, '2012-06-13 05:06:25.9', 'quarter') from datetable +POSTHOOK: type: QUERY +POSTHOOK: Input: default@datetable +#### A masked pattern was here #### +2 +2 +2 +2 +-85 +-83 +-83 +-9 +PREHOOK: query: drop table datetable +PREHOOK: type: DROPTABLE +PREHOOK: Input: default@datetable +PREHOOK: Output: default@datetable +POSTHOOK: query: drop table datetable +POSTHOOK: type: DROPTABLE +POSTHOOK: Input: default@datetable +POSTHOOK: Output: default@datetable +PREHOOK: query: create table timestamptable (date1 Timestamp, date2 Timestamp) row format delimited fields terminated by ', ' +PREHOOK: type: CREATETABLE +POSTHOOK: query: create table timestamptable (date1 Timestamp, date2 Timestamp) row format delimited fields terminated by ', ' +POSTHOOK: type: CREATETABLE +POSTHOOK: Output: default@timestamptable +PREHOOK: query: load data local inpath '../data/files/timestamptable.txt' overwrite into table timestamptable +PREHOOK: type: LOAD +PREHOOK: Output: default@timestamptable +POSTHOOK: query: load data local inpath '../data/files/timestamptable.txt' overwrite into table timestamptable +POSTHOOK: type: LOAD +POSTHOOK: Output: default@timestamptable +PREHOOK: query: select * from timestamptable +PREHOOK: type: QUERY +PREHOOK: Input: default@timestamptable +#### A masked pattern was here #### +POSTHOOK: query: select * from timestamptable +POSTHOOK: type: QUERY +POSTHOOK: Input: default@timestamptable +#### A masked pattern was here #### +2012-03-09 05:23:01 2012-12-05 09:08:00.096 +2012-06-23 05:06:09 2012-12-05 05:16:09 +2012-12-19 08:19:19 2012-12-15 08:10:19 +2012-07-24 08:00:00 2012-12-05 09:51:00 +2012-02-01 07:07:07 1991-02-01 07:07:07 +2015-06-10 23:59:00 1991-09-10 23:59:00.7 +1991-07-03 05:23:29 1991-07-03 05:23:29.356 +PREHOOK: query: select datediff(date1, date2, 'day') from timestamptable +PREHOOK: type: QUERY +PREHOOK: Input: default@timestamptable +#### A masked pattern was here #### +POSTHOOK: query: select datediff(date1, date2, 'day') from timestamptable +POSTHOOK: type: QUERY +POSTHOOK: Input: default@timestamptable +#### A masked pattern was here #### +-271 +-165 +4 +-134 +7670 +8673 +0 +PREHOOK: query: select datediff(date1, date2, 'week') from timestamptable +PREHOOK: type: QUERY +PREHOOK: Input: default@timestamptable +#### A masked pattern was here #### +POSTHOOK: query: select datediff(date1, date2, 'week') from timestamptable +POSTHOOK: type: QUERY +POSTHOOK: Input: default@timestamptable +#### A masked pattern was here #### +-38 +-23 +0 +-19 +1095 +1239 +0 +PREHOOK: query: select datediff(date1, date2, 'year') from timestamptable +PREHOOK: type: QUERY +PREHOOK: Input: default@timestamptable +#### A masked pattern was here #### +POSTHOOK: query: select datediff(date1, date2, 'year') from timestamptable +POSTHOOK: type: QUERY +POSTHOOK: Input: default@timestamptable +#### A masked pattern was here #### +0 +0 +0 +0 +21 +23 +0 +PREHOOK: query: select datediff(date1, date2, 'quarter') from timestamptable +PREHOOK: type: QUERY +PREHOOK: Input: default@timestamptable +#### A masked pattern was here #### +POSTHOOK: query: select datediff(date1, date2, 'quarter') from timestamptable +POSTHOOK: type: QUERY +POSTHOOK: Input: default@timestamptable +#### A masked pattern was here #### +-3 +-2 +0 +-1 +84 +95 +0 +PREHOOK: query: drop table timestamptable +PREHOOK: type: DROPTABLE +PREHOOK: Input: default@timestamptable +PREHOOK: Output: default@timestamptable +POSTHOOK: query: drop table timestamptable +POSTHOOK: type: DROPTABLE +POSTHOOK: Input: default@timestamptable +POSTHOOK: Output: default@timestamptable +PREHOOK: query: create table timestamptable (date1 string, date2 Timestamp) row format delimited fields terminated by ', ' +PREHOOK: type: CREATETABLE +POSTHOOK: query: create table timestamptable (date1 string, date2 Timestamp) row format delimited fields terminated by ', ' +POSTHOOK: type: CREATETABLE +POSTHOOK: Output: default@timestamptable +PREHOOK: query: load data local inpath '../data/files/timestamptable.txt' overwrite into table timestamptable +PREHOOK: type: LOAD +PREHOOK: Output: default@timestamptable +POSTHOOK: query: load data local inpath '../data/files/timestamptable.txt' overwrite into table timestamptable +POSTHOOK: type: LOAD +POSTHOOK: Output: default@timestamptable +PREHOOK: query: select * from timestamptable +PREHOOK: type: QUERY +PREHOOK: Input: default@timestamptable +#### A masked pattern was here #### +POSTHOOK: query: select * from timestamptable +POSTHOOK: type: QUERY +POSTHOOK: Input: default@timestamptable +#### A masked pattern was here #### +2012-03-09 05:23:01 2012-12-05 09:08:00.096 +2012-06-23 05:06:09 2012-12-05 05:16:09 +2012-12-19 08:19:19 2012-12-15 08:10:19 +2012-07-24 08:00:00 2012-12-05 09:51:00 +2012-02-01 07:07:07 1991-02-01 07:07:07 +2015-06-10 23:59:00 1991-09-10 23:59:00.7 +1991-07-03 05:23:29 1991-07-03 05:23:29.356 +PREHOOK: query: select datediff(date1, date2, 'day') from timestamptable +PREHOOK: type: QUERY +PREHOOK: Input: default@timestamptable +#### A masked pattern was here #### +POSTHOOK: query: select datediff(date1, date2, 'day') from timestamptable +POSTHOOK: type: QUERY +POSTHOOK: Input: default@timestamptable +#### A masked pattern was here #### +-271 +-165 +3 +-134 +7669 +8672 +0 +PREHOOK: query: select datediff(date1, date2, 'week') from timestamptable +PREHOOK: type: QUERY +PREHOOK: Input: default@timestamptable +#### A masked pattern was here #### +POSTHOOK: query: select datediff(date1, date2, 'week') from timestamptable +POSTHOOK: type: QUERY +POSTHOOK: Input: default@timestamptable +#### A masked pattern was here #### +-38 +-23 +0 +-19 +1095 +1238 +0 +PREHOOK: query: select datediff(date1, date2, 'year') from timestamptable +PREHOOK: type: QUERY +PREHOOK: Input: default@timestamptable +#### A masked pattern was here #### +POSTHOOK: query: select datediff(date1, date2, 'year') from timestamptable +POSTHOOK: type: QUERY +POSTHOOK: Input: default@timestamptable +#### A masked pattern was here #### +0 +0 +0 +0 +20 +23 +0 +PREHOOK: query: select datediff(date1, date2, 'quarter') from timestamptable +PREHOOK: type: QUERY +PREHOOK: Input: default@timestamptable +#### A masked pattern was here #### +POSTHOOK: query: select datediff(date1, date2, 'quarter') from timestamptable +POSTHOOK: type: QUERY +POSTHOOK: Input: default@timestamptable +#### A masked pattern was here #### +-3 +-2 +0 +-1 +83 +95 +0 +PREHOOK: query: drop table timestamptable +PREHOOK: type: DROPTABLE +PREHOOK: Input: default@timestamptable +PREHOOK: Output: default@timestamptable +POSTHOOK: query: drop table timestamptable +POSTHOOK: type: DROPTABLE +POSTHOOK: Input: default@timestamptable +POSTHOOK: Output: default@timestamptable Index: ql/src/test/queries/clientnegative/udf_datediff.q =================================================================== --- ql/src/test/queries/clientnegative/udf_datediff.q (revision 0) +++ ql/src/test/queries/clientnegative/udf_datediff.q (revision 0) @@ -0,0 +1,10 @@ +DESCRIBE FUNCTION datediff; +DESCRIBE FUNCTION EXTENDED datediff; + +create table datetable (date1 string, date2 string) row format delimited fields terminated by ', '; +load data local inpath '../data/files/datetable.txt' overwrite into table datetable; +select * from datetable; +select datediff(date1, date2, 'foo') from datetable; +select datediff(date1, 'foo') from datetable; +select datediff() from datetable; +drop table datetable; \ No newline at end of file Index: ql/src/test/queries/clientpositive/udf_datediff.q =================================================================== --- ql/src/test/queries/clientpositive/udf_datediff.q (revision 1362724) +++ ql/src/test/queries/clientpositive/udf_datediff.q (working copy) @@ -1,2 +1,36 @@ DESCRIBE FUNCTION datediff; DESCRIBE FUNCTION EXTENDED datediff; + +create table datetable (date1 string, date2 string) row format delimited fields terminated by ', '; +load data local inpath '../data/files/datetable.txt' overwrite into table datetable; +select * from datetable; +select datediff(date1, date2, 'day') from datetable; +select datediff(date1, '2012-06-13', 'hour') from datetable; +select datediff(date1, '2012-06-13', 'year') from datetable; +select datediff(date1, '2012-06-13', 'quarter') from datetable; +select datediff(date1, '2012-06-13', 'month') from datetable; +select datediff(date1, '2012-06-13', 'week') from datetable; +select datediff('2012-06-13', date2, 'minute') from datetable; +select datediff(date1, '2012-06-13 05:06:25', 'minute') from datetable; +select datediff(date1, '2012-06-13 05:06:25', 'second') from datetable; +select datediff(date1, '2012-06-13 05:06:25', 'millisecond') from datetable; +select datediff(date1, '2012-06-13 05:06:25.9', 'millisecond') from datetable; +select datediff(date1, '2012-06-13 05:06:25.9', 'microsecond') from datetable; +select datediff(date2, '2012-06-13 05:06:25.9', 'quarter') from datetable; +drop table datetable; +create table timestamptable (date1 Timestamp, date2 Timestamp) row format delimited fields terminated by ', '; +load data local inpath '../data/files/timestamptable.txt' overwrite into table timestamptable; +select * from timestamptable; +select datediff(date1, date2, 'day') from timestamptable; +select datediff(date1, date2, 'week') from timestamptable; +select datediff(date1, date2, 'year') from timestamptable; +select datediff(date1, date2, 'quarter') from timestamptable; +drop table timestamptable; +create table timestamptable (date1 string, date2 Timestamp) row format delimited fields terminated by ', '; +load data local inpath '../data/files/timestamptable.txt' overwrite into table timestamptable; +select * from timestamptable; +select datediff(date1, date2, 'day') from timestamptable; +select datediff(date1, date2, 'week') from timestamptable; +select datediff(date1, date2, 'year') from timestamptable; +select datediff(date1, date2, 'quarter') from timestamptable; +drop table timestamptable; \ No newline at end of file Index: ql/src/java/org/apache/hadoop/hive/ql/udf/UDFDateDiff.java =================================================================== --- ql/src/java/org/apache/hadoop/hive/ql/udf/UDFDateDiff.java (revision 1362724) +++ ql/src/java/org/apache/hadoop/hive/ql/udf/UDFDateDiff.java (working copy) @@ -20,13 +20,17 @@ import java.text.ParseException; import java.text.SimpleDateFormat; +import java.util.ArrayList; +import java.util.Calendar; import java.util.Date; import java.util.TimeZone; import org.apache.hadoop.hive.ql.exec.Description; import org.apache.hadoop.hive.ql.exec.UDF; +import org.apache.hadoop.hive.ql.exec.UDFArgumentException; import org.apache.hadoop.hive.serde2.io.TimestampWritable; import org.apache.hadoop.io.IntWritable; +import org.apache.hadoop.io.LongWritable; import org.apache.hadoop.io.Text; /** @@ -34,20 +38,37 @@ * */ @Description(name = "datediff", - value = "_FUNC_(date1, date2) - Returns the number of days between date1 and date2", - extended = "date1 and date2 are strings in the format " - + "'yyyy-MM-dd HH:mm:ss' or 'yyyy-MM-dd'. The time parts are ignored." - + "If date1 is earlier than date2, the result is negative.\n" - + "Example:\n " - + " > SELECT _FUNC_('2009-30-07', '2009-31-07') FROM src LIMIT 1;\n" - + " 1") + value = "_FUNC_(date1, date2) - Returns the number of days between date1 and date2" + + + "\n_FUNC_(date1, date2, unit) - Returns the difference between date1 and date2 in the units specified.", + extended = "date1 and date2 are strings/text or timestamps in the format and unit is a string/text" + + "'yyyy-MM-dd HH:mm:ss', 'yyyy-MM-dd', 'yyyy-MM-dd HH:mm:ss.milli', or 'yyyy-MM-dd'." + + "If date1 is earlier than date2, the result is negative.\n" + + "Example:\n " + + " > SELECT _FUNC_('2009-30-07', '2009-31-07', 'day') FROM src;\n" + + " -1") public class UDFDateDiff extends UDF { private final SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd"); + private final ArrayList dateFormats; private final IntWritable result = new IntWritable(); + private final LongWritable resultLong = new LongWritable(); + public enum Unit { + MICROSECOND, MILLISECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR + } + public UDFDateDiff() { + dateFormats = new ArrayList(); + dateFormats.add(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS")); + dateFormats.add(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss")); + dateFormats.add(formatter); + dateFormats.add(new SimpleDateFormat("HH:mm:ss.SSS")); + dateFormats.add(new SimpleDateFormat("HH:mm:ss")); formatter.setTimeZone(TimeZone.getTimeZone("UTC")); + for (SimpleDateFormat sdf : dateFormats) { + sdf.setTimeZone(TimeZone.getTimeZone("UTC")); + } } /** @@ -92,6 +113,173 @@ return result; } + /** + * Returns the difference between the two parameters entered in the units provided in the third + * parameter. + * + * @param dateString1 + * @param dateString2 + * @param unitText + * @return + * @throws UDFArgumentException + */ + public LongWritable evaluate(Text dateString1, Text dateString2, Text unitText) + throws UDFArgumentException { + return evaluateObj(dateString1, dateString2, unitText); + } + + /** + * Returns the difference between the two parameters entered in the units provided in the third + * parameter. + * + * @param t1 + * @param t2 + * @param unitText + * @return + * @throws UDFArgumentException + */ + public LongWritable evaluate(TimestampWritable t1, TimestampWritable t2, Text unitText) + throws UDFArgumentException { + return evaluateObj(t1, t2, unitText); + } + + /** + * Returns the difference between the two parameters entered in the units provided in the third + * parameter. + * + * @param t + * @param dateString + * @param unitText + * @return + * @throws UDFArgumentException + */ + public LongWritable evaluate(TimestampWritable t, Text dateString, Text unitText) + throws UDFArgumentException { + return evaluateObj(t, dateString, unitText); + } + + /** + * Returns the difference between the two parameters entered in the units provided in the third + * parameter. + * + * @param dateString + * @param t + * @param unitText + * @return + * @throws UDFArgumentException + */ + public LongWritable evaluate(Text dateString, TimestampWritable t, Text unitText) + throws UDFArgumentException { + return evaluateObj(dateString, t, unitText); + } + + private LongWritable evaluateObj(Object dateObj1, Object dateObj2, Text unitText) + throws UDFArgumentException + { + Unit unit = toUnit(unitText); + Date date1 = new Date(); + Date date2 = new Date(); + date1 = resolveDate(dateObj1, unit); + date2 = resolveDate(dateObj2, unit); + if (date1 == null || date2 == null) { + return null; + } + Calendar cal1 = Calendar.getInstance(); + cal1.setTime(date1); + Calendar cal2 = Calendar.getInstance(); + cal2.setTime(date2); + int month1 = 12 * cal1.get(Calendar.YEAR) + cal1.get(Calendar.MONTH); + int month2 = 12 * cal2.get(Calendar.YEAR) + cal2.get(Calendar.MONTH); + long diffInMilliSeconds = date1.getTime() - date2.getTime(); + switch (unit) { + case MICROSECOND: + resultLong.set(diffInMilliSeconds * 1000); + return resultLong; + case MILLISECOND: + resultLong.set(diffInMilliSeconds); + return resultLong; + case SECOND: + resultLong.set((diffInMilliSeconds / 1000)); + return resultLong; + case MINUTE: + resultLong.set((diffInMilliSeconds / (60 * 1000))); + return resultLong; + case HOUR: + resultLong.set((diffInMilliSeconds / (1000 * 60 * 60))); + return resultLong; + case WEEK: + resultLong.set((diffInMilliSeconds / (86400 * 1000 * 7))); + return resultLong; + case MONTH: + resultLong.set((month1 - month2)); + return resultLong; + case QUARTER: + resultLong.set(((month1 - month2) / 3)); + return resultLong; + case YEAR: + int monthdiff = month1 - month2; + resultLong.set((monthdiff / 12)); + return resultLong; + default: // day + resultLong.set((diffInMilliSeconds / (86400 * 1000))); + return resultLong; + } + } + + private Date resolveDate(Object dateObj, Unit unit) throws UDFArgumentException + { + Date date = new Date(); + boolean unitFlag = false; + if (unit == Unit.DAY || unit == Unit.WEEK || unit == Unit.MONTH || unit == Unit.QUARTER + || unit == Unit.YEAR) { + unitFlag = true; + } + if (dateObj instanceof String) + { + if (unitFlag) { + return toDate((String) dateObj); + } else { + return toTimestamp((String) dateObj); + } + } + if (dateObj instanceof Text) + { + if (unitFlag) { + return toDate((Text) dateObj); + } else { + return toTimestamp((Text) dateObj); + } + } + if (dateObj instanceof TimestampWritable) + { + if (unitFlag) { + return toDate((TimestampWritable) dateObj); + } else { + return toTimestamp((TimestampWritable) dateObj); + } + } + if (dateObj instanceof Date) + { + if (unitFlag) { + return (Date) dateObj; + } else { + return toTimestamp((TimestampWritable) dateObj); + } + } + return date; + } + + private Date formatLong(String dateString) throws UDFArgumentException { + for (SimpleDateFormat sdf : dateFormats) + { + try { + return sdf.parse(dateString); + } catch (ParseException e) { + } + } + throw new UDFArgumentException(dateString + " is not a properly formatted date."); + } + private Date format(String dateString) { try { return formatter.parse(dateString); @@ -114,4 +302,83 @@ return t.getTimestamp(); } + private Date toDate(String dateString) { + if (dateString == null) { + return null; + } + return format(dateString); + } + + private Date toDate(Date date) { + if (date == null) { + return null; + } + return date; + } + + private Date toTimestamp(Text dateString) throws UDFArgumentException { + if (dateString == null) { + return null; + } + return formatLong(dateString.toString()); + } + + private Date toTimestamp(TimestampWritable t) { + if (t == null) { + return null; + } + return t.getTimestamp(); + } + + private Date toTimestamp(String dateString) throws UDFArgumentException { + if (dateString == null) { + return null; + } + return formatLong(dateString); + } + + private Date toTimestamp(Date date) { + if (date == null) { + return null; + } + return date; + } + + private Unit toUnit(Text unitText) + { + if (unitText == null) { + return Unit.DAY; + } + String unitString = unitText.toString().toLowerCase(); + + if (unitString.equals("microsecond")) { + return Unit.MICROSECOND; + } + if (unitString.equals("millisecond")) { + return Unit.MILLISECOND; + } + if (unitString.equals("second")) { + return Unit.SECOND; + } + if (unitString.equals("minute")) { + return Unit.MINUTE; + } + if (unitString.equals("hour")) { + return Unit.HOUR; + } + if (unitString.equals("week")) { + return Unit.WEEK; + } + if (unitString.equals("month")) { + return Unit.MONTH; + } + if (unitString.equals("quarter")) { + return Unit.QUARTER; + } + if (unitString.equals("year")) { + return Unit.YEAR; + } else { + return Unit.DAY; + } + } }