IMPALA-5316: Adds last_day() function
This change adds last_day() function.
The function takes exactly one TIMESTAMP argument
and returns a TIMESTAMP that is the last date of the
input date's calendar month.
The function will return NULL when:
1) The input argument cannot be implicitly casted to
a TIMESTAMP.
2) The TIMESTAMP argument is missing a date component.
3) The TIMESTAMP argument is outside of the supported range:
between 1400-01-31 00:00:00 and 9999-12-31 23:59:59
Change-Id: I429c8734bddca3c37a2eedc211a16a4ffcb04370
Reviewed-on: http://gerrit.cloudera.org:8080/6991
Reviewed-by: Matthew Jacobs <[email protected]>
Tested-by: Impala Public Jenkins
Project: http://git-wip-us.apache.org/repos/asf/incubator-impala/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-impala/commit/d5b6cb90
Tree: http://git-wip-us.apache.org/repos/asf/incubator-impala/tree/d5b6cb90
Diff: http://git-wip-us.apache.org/repos/asf/incubator-impala/diff/d5b6cb90
Branch: refs/heads/master
Commit: d5b6cb903de24c95de9000c473a6ddfca8516412
Parents: df2b5a9
Author: Vincent Tran <[email protected]>
Authored: Thu May 25 08:02:00 2017 -0400
Committer: Impala Public Jenkins <[email protected]>
Committed: Thu Jun 15 04:51:49 2017 +0000
----------------------------------------------------------------------
be/src/exprs/expr-test.cc | 53 +++++++++++++++++++++++
be/src/exprs/timestamp-functions-ir.cc | 12 +++++
be/src/exprs/timestamp-functions.h | 9 ++++
common/function-registry/impala_functions.py | 1 +
4 files changed, 75 insertions(+)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-impala/blob/d5b6cb90/be/src/exprs/expr-test.cc
----------------------------------------------------------------------
diff --git a/be/src/exprs/expr-test.cc b/be/src/exprs/expr-test.cc
index dcbdeef..2aa4537 100644
--- a/be/src/exprs/expr-test.cc
+++ b/be/src/exprs/expr-test.cc
@@ -276,6 +276,59 @@ class ExprTest : public testing::Test {
return results;
}
+ void TestLastDayFunction() {
+ // Test common months (with and without time component).
+ TestTimestampValue("last_day('2003-01-02 04:24:04.1579')",
+ TimestampValue::Parse("2003-01-31 00:00:00", 19));
+ TestTimestampValue("last_day('2003-02-02')",
+ TimestampValue::Parse("2003-02-28 00:00:00"));
+ TestTimestampValue("last_day('2003-03-02 03:21:12.0058')",
+ TimestampValue::Parse("2003-03-31 00:00:00"));
+ TestTimestampValue("last_day('2003-04-02')",
+ TimestampValue::Parse("2003-04-30 00:00:00"));
+ TestTimestampValue("last_day('2003-05-02')",
+ TimestampValue::Parse("2003-05-31 00:00:00"));
+ TestTimestampValue("last_day('2003-06-02')",
+ TimestampValue::Parse("2003-06-30 00:00:00"));
+ TestTimestampValue("last_day('2003-07-02 00:01:01.125')",
+ TimestampValue::Parse("2003-07-31 00:00:00"));
+ TestTimestampValue("last_day('2003-08-02')",
+ TimestampValue::Parse("2003-08-31 00:00:00"));
+ TestTimestampValue("last_day('2003-09-02')",
+ TimestampValue::Parse("2003-09-30 00:00:00"));
+ TestTimestampValue("last_day('2003-10-02')",
+ TimestampValue::Parse("2003-10-31 00:00:00"));
+ TestTimestampValue("last_day('2003-11-02 12:30:16')",
+ TimestampValue::Parse("2003-11-30 00:00:00"));
+ TestTimestampValue("last_day('2003-12-02')",
+ TimestampValue::Parse("2003-12-31 00:00:00"));
+
+ // Test leap years and special cases.
+ TestTimestampValue("last_day('2004-02-13')",
+ TimestampValue::Parse("2004-02-29 00:00:00"));
+ TestTimestampValue("last_day('2008-02-13')",
+ TimestampValue::Parse("2008-02-29 00:00:00"));
+ TestTimestampValue("last_day('2000-02-13')",
+ TimestampValue::Parse("2000-02-29 00:00:00"));
+ TestTimestampValue("last_day('1900-02-13')",
+ TimestampValue::Parse("1900-02-28 00:00:00"));
+ TestTimestampValue("last_day('2100-02-13')",
+ TimestampValue::Parse("2100-02-28 00:00:00"));
+
+ // Test corner cases.
+ TestTimestampValue("last_day('1400-01-01 00:00:00')",
+ TimestampValue::Parse("1400-01-31 00:00:00"));
+ TestTimestampValue("last_day('9999-12-31 23:59:59')",
+ TimestampValue::Parse("9999-12-31 00:00:00"));
+
+ // Test invalid input.
+ TestIsNull("last_day('12202010')", TYPE_TIMESTAMP);
+ TestIsNull("last_day('')", TYPE_TIMESTAMP);
+ TestIsNull("last_day(NULL)", TYPE_TIMESTAMP);
+ TestIsNull("last_day('02-13-2014')", TYPE_TIMESTAMP);
+ TestIsNull("last_day('00:00:00')", TYPE_TIMESTAMP);
+ }
+
void TestNextDayFunction() {
// Sequential test cases
TestTimestampValue("next_day('2016-05-01','Sunday')",
http://git-wip-us.apache.org/repos/asf/incubator-impala/blob/d5b6cb90/be/src/exprs/timestamp-functions-ir.cc
----------------------------------------------------------------------
diff --git a/be/src/exprs/timestamp-functions-ir.cc
b/be/src/exprs/timestamp-functions-ir.cc
index b1aae03..6a25ced 100644
--- a/be/src/exprs/timestamp-functions-ir.cc
+++ b/be/src/exprs/timestamp-functions-ir.cc
@@ -38,6 +38,7 @@ using boost::gregorian::max_date_time;
using boost::gregorian::min_date_time;
using boost::posix_time::not_a_date_time;
using boost::posix_time::ptime;
+using boost::posix_time::time_duration;
using namespace impala_udf;
using namespace strings;
@@ -564,6 +565,17 @@ TimestampVal TimestampFunctions::NextDay(FunctionContext*
context,
return AddSub<true, IntVal, Days, false>(context, date, delta);
}
+TimestampVal TimestampFunctions::LastDay(FunctionContext* context,
+ const TimestampVal& ts) {
+ if (ts.is_null) return TimestampVal::null();
+ const TimestampValue& timestamp = TimestampValue::FromTimestampVal(ts);
+ if (!timestamp.HasDate()) return TimestampVal::null();
+ TimestampValue tsv(timestamp.date().end_of_month(), time_duration(0,0,0,0));
+ TimestampVal rt_date;
+ tsv.ToTimestampVal(&rt_date);
+ return rt_date;
+}
+
IntVal TimestampFunctions::IntMonthsBetween(FunctionContext* context,
const TimestampVal& ts1, const TimestampVal& ts2) {
if (ts1.is_null || ts2.is_null) return IntVal::null();
http://git-wip-us.apache.org/repos/asf/incubator-impala/blob/d5b6cb90/be/src/exprs/timestamp-functions.h
----------------------------------------------------------------------
diff --git a/be/src/exprs/timestamp-functions.h
b/be/src/exprs/timestamp-functions.h
index 3c48567..b02e7bd 100644
--- a/be/src/exprs/timestamp-functions.h
+++ b/be/src/exprs/timestamp-functions.h
@@ -199,6 +199,15 @@ class TimestampFunctions {
static TimestampVal AddSub(FunctionContext* context, const TimestampVal&
timestamp,
const AnyIntVal& num_interval_units);
+ /// Return the last date in the month of a specified input date.
+ /// The TIMESTAMP argument requires a date component,
+ /// it may or may not have a time component.
+ /// The function will return a NULL TimestampVal when:
+ /// 1) The TIMESTAMP argument is missing a date component.
+ /// 2) The TIMESTAMP argument is outside of the supported range:
+ /// between 1400-01-31 00:00:00 and 9999-12-31 23:59:59
+ static TimestampVal LastDay(FunctionContext* context, const TimestampVal&
ts);
+
/// Helper function to check date/time format strings.
/// TODO: eventually return format converted from Java to Boost.
static StringValue* CheckFormat(StringValue* format);
http://git-wip-us.apache.org/repos/asf/incubator-impala/blob/d5b6cb90/common/function-registry/impala_functions.py
----------------------------------------------------------------------
diff --git a/common/function-registry/impala_functions.py
b/common/function-registry/impala_functions.py
index 8f8f07e..b80261c 100644
--- a/common/function-registry/impala_functions.py
+++ b/common/function-registry/impala_functions.py
@@ -110,6 +110,7 @@ visible_functions = [
# Timestamp functions
[['next_day'], 'TIMESTAMP', ['TIMESTAMP', 'STRING'],
'_ZN6impala18TimestampFunctions7NextDayEPN10impala_udf15FunctionContextERKNS1_12TimestampValERKNS1_9StringValE'],
+ [['last_day'], 'TIMESTAMP', ['TIMESTAMP'],
'_ZN6impala18TimestampFunctions7LastDayEPN10impala_udf15FunctionContextERKNS1_12TimestampValE'],
[['unix_timestamp'], 'BIGINT', ['STRING'],
'_ZN6impala18TimestampFunctions14UnixFromStringEPN10impala_udf15FunctionContextERKNS1_9StringValE'],
[['year'], 'INT', ['TIMESTAMP'],
'_ZN6impala18TimestampFunctions4YearEPN10impala_udf15FunctionContextERKNS1_12TimestampValE'],
[['month'], 'INT', ['TIMESTAMP'],
'_ZN6impala18TimestampFunctions5MonthEPN10impala_udf15FunctionContextERKNS1_12TimestampValE'],
