Repository: trafodion Updated Branches: refs/heads/master 20f8240e4 -> 62353a2cd
tochar enhancement Project: http://git-wip-us.apache.org/repos/asf/trafodion/repo Commit: http://git-wip-us.apache.org/repos/asf/trafodion/commit/5c2c5d24 Tree: http://git-wip-us.apache.org/repos/asf/trafodion/tree/5c2c5d24 Diff: http://git-wip-us.apache.org/repos/asf/trafodion/diff/5c2c5d24 Branch: refs/heads/master Commit: 5c2c5d245d3a63bb368615d173be71fa1ebb1846 Parents: 23a7be1 Author: kakaxi3019 <[email protected]> Authored: Fri Jul 6 16:46:29 2018 +0800 Committer: kakaxi3019 <[email protected]> Committed: Fri Jul 6 16:46:29 2018 +0800 ---------------------------------------------------------------------- core/sql/exp/exp_datetime.cpp | 237 ++++++++++++++++++++++++++++++ core/sql/exp/exp_datetime.h | 43 +++++- core/sql/exp/exp_function.cpp | 24 +-- core/sql/optimizer/BindItemExpr.cpp | 7 +- core/sql/regress/seabase/EXPECTED030 | 168 +++++++++++++++++++++ core/sql/regress/seabase/TEST030 | 21 +++ 6 files changed, 475 insertions(+), 25 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/trafodion/blob/5c2c5d24/core/sql/exp/exp_datetime.cpp ---------------------------------------------------------------------- diff --git a/core/sql/exp/exp_datetime.cpp b/core/sql/exp/exp_datetime.cpp index 117495d..926cde2 100644 --- a/core/sql/exp/exp_datetime.cpp +++ b/core/sql/exp/exp_datetime.cpp @@ -160,10 +160,54 @@ const ExpDatetime::DatetimeFormatInfo ExpDatetime::datetimeFormat[] = {ExpDatetime::DATETIME_FORMAT_NUM1, "99:99:99:99", 11, 11}, {ExpDatetime::DATETIME_FORMAT_NUM2, "-99:99:99:99", 12, 12}, + {ExpDatetime::DATETIME_FORMAT_EXTRA_HH, "HH", 2, 2}, + {ExpDatetime::DATETIME_FORMAT_EXTRA_HH12,"HH12", 2, 2}, + {ExpDatetime::DATETIME_FORMAT_EXTRA_HH24,"HH24", 2, 2}, + {ExpDatetime::DATETIME_FORMAT_EXTRA_MI, "MI", 2, 2}, + {ExpDatetime::DATETIME_FORMAT_EXTRA_SS, "SS", 2, 2}, + {ExpDatetime::DATETIME_FORMAT_EXTRA_YYYY,"YYYY", 4, 4}, + {ExpDatetime::DATETIME_FORMAT_EXTRA_YYY, "YYY", 3, 3}, + {ExpDatetime::DATETIME_FORMAT_EXTRA_YY, "YY", 2, 2}, + {ExpDatetime::DATETIME_FORMAT_EXTRA_Y, "Y", 1, 1}, + {ExpDatetime::DATETIME_FORMAT_EXTRA_MON, "MON", 3, 3}, + {ExpDatetime::DATETIME_FORMAT_EXTRA_MM, "MM", 2, 2}, + {ExpDatetime::DATETIME_FORMAT_EXTRA_DY, "DY", 3, 3}, + {ExpDatetime::DATETIME_FORMAT_EXTRA_DAY, "DAY", 6, 9}, + {ExpDatetime::DATETIME_FORMAT_EXTRA_CC, "CC", 2, 2}, + {ExpDatetime::DATETIME_FORMAT_EXTRA_D, "D", 1, 1}, + {ExpDatetime::DATETIME_FORMAT_EXTRA_DD, "DD", 2, 2}, + {ExpDatetime::DATETIME_FORMAT_EXTRA_DDD, "DDD", 1, 3}, + {ExpDatetime::DATETIME_FORMAT_EXTRA_W, "W", 1, 1}, + {ExpDatetime::DATETIME_FORMAT_EXTRA_WW, "WW", 1, 2}, + {ExpDatetime::DATETIME_FORMAT_EXTRA_J, "J", 7, 7}, + {ExpDatetime::DATETIME_FORMAT_EXTRA_Q, "Q", 1, 1}, + // formats that are replaced by one of the other formats at bind time {ExpDatetime::DATETIME_FORMAT_UNSPECIFIED, "UNSPECIFIED", 11, 11} }; +UInt32 Date2Julian(int y, int m ,int d) +{ + int myjulian = 0; + int mycentury = 0; + if ( m <= 2) + { + m = m+13; + y = y+4799; + } + else + { + m = m+1; + y = y+4800; + } + + mycentury = y / 100; + myjulian = y * 365 - 32167; + myjulian += y/4 - mycentury + mycentury / 4; + myjulian += 7834 * m / 256 + d; + return myjulian; +} + ExpDatetime::ExpDatetime() { setClassID(SimpleTypeID); @@ -3294,6 +3338,39 @@ convertMonthToStr(Lng32 value, char *&result, UInt32 width) result += width; } +static void +convertDayOfWeekToStr(Lng32 value, char *&result, NABoolean bAbbreviation, UInt32 width) +{ + const char* dayofweek[] = + { + "SUNDAY ", + "MONDAY ", + "TUESDAY ", + "WEDNESDAY", + "THURSDAY ", + "FRIDAY ", + "SATURDAY " + }; + + const char* dayofweek_abb[] = + { + "SUN", + "MON", + "TUE", + "WED", + "THU", + "FRI", + "SAT" + }; + + if (bAbbreviation) + strcpy(result, dayofweek_abb[value-1]); + else + strcpy(result, dayofweek[value-1]); + // Update result pointer to point to end of string. + result += width; +} + static void convertMonthToStrLongFormat(Lng32 value, char *&result, UInt32 width) { @@ -3600,10 +3677,170 @@ ExpDatetime::convDatetimeToASCII(char *srcData, } break; + case DATETIME_FORMAT_EXTRA_HH: + case DATETIME_FORMAT_EXTRA_HH24: + case DATETIME_FORMAT_EXTRA_HH12: + { + char hour = *srcData++; + if ( DATETIME_FORMAT_EXTRA_HH12 == format ) + { + if (hour > 12) + hour = hour - 12; + } + convertToAscii(hour, dstDataPtr, 2); + return (dstDataPtr - dstData); + } + break; + + case DATETIME_FORMAT_EXTRA_MI: + { + char minute = *(srcData+1); + convertToAscii(minute, dstDataPtr, 2); + return (dstDataPtr - dstData); + } + break; + + case DATETIME_FORMAT_EXTRA_SS: + { + char second = *(srcData+2); + convertToAscii(second, dstDataPtr, 2); + return (dstDataPtr - dstData); + } + break; + + case DATETIME_FORMAT_EXTRA_YYYY: + case DATETIME_FORMAT_EXTRA_YYY: + case DATETIME_FORMAT_EXTRA_YY: + case DATETIME_FORMAT_EXTRA_Y: + { + UInt32 nw = 4; //DATETIME_FORMAT_EXTRA_YYYY + if ( DATETIME_FORMAT_EXTRA_YYY == format ) + { + nw = 3; + year = year % 1000; + } + else if ( DATETIME_FORMAT_EXTRA_YY == format ) + { + nw = 2; + year = year % 100; + } + else if ( DATETIME_FORMAT_EXTRA_Y == format ) + { + nw = 1; + year = year % 10; + } + convertToAscii(year, dstDataPtr, nw); + return (dstDataPtr - dstData); + } + break; + case DATETIME_FORMAT_EXTRA_CC: + { + year = (year+99)/100; + convertToAscii(year, dstDataPtr,2); + return (dstDataPtr - dstData); + } + break; + case DATETIME_FORMAT_EXTRA_MON: + case DATETIME_FORMAT_EXTRA_MM: + { + if (DATETIME_FORMAT_EXTRA_MM == format) + convertToAscii(month, dstDataPtr,2); + else if (DATETIME_FORMAT_EXTRA_MON == format) + { + if (0 == month) + return -1; + convertMonthToStr(month, dstDataPtr, 3); + } + return (dstDataPtr - dstData); + } + break; + case DATETIME_FORMAT_EXTRA_DY: + case DATETIME_FORMAT_EXTRA_DAY: + case DATETIME_FORMAT_EXTRA_D: + { + Int64 interval = getTotalDays(year, month, day); + short dayofweek = (short)(((interval + 1) % 7) + 1); + if (DATETIME_FORMAT_EXTRA_D == format) + { + convertToAscii(dayofweek,dstDataPtr,1); + } + else if (DATETIME_FORMAT_EXTRA_DAY == format + || DATETIME_FORMAT_EXTRA_DY == format) + { + if (0 == day) + return -1; + //SUNDAY or SUN + NABoolean bAbbr = (DATETIME_FORMAT_EXTRA_DY == format ? TRUE:FALSE); + UInt32 width = 9; + if (bAbbr) + width = 3; + convertDayOfWeekToStr(dayofweek, dstDataPtr, bAbbr, width); + } + return (dstDataPtr - dstData); + } + break; + case DATETIME_FORMAT_EXTRA_DD: + { + convertToAscii(day, dstDataPtr, 2); + return (dstDataPtr - dstData); + } + break; + case DATETIME_FORMAT_EXTRA_DDD: + { + int dayofyear = 0; + if( day ) + dayofyear = Date2Julian(year,month,day)-Date2Julian(year,1,1)+1; + convertToAscii(dayofyear,dstDataPtr,3); + return (dstDataPtr - dstData); + } + break; + case DATETIME_FORMAT_EXTRA_W: + { + int weekofmonth = 0; + if (day) + weekofmonth = (day-1)/7+1; + convertToAscii(weekofmonth,dstDataPtr,1); + return (dstDataPtr - dstData); + } + break; + case DATETIME_FORMAT_EXTRA_WW: + { + //same with built-in function week + int weekofmonth = 0; + if ( day ) + { + Int64 interval = getTotalDays(year, 1, 1); + int dayofweek = (int)(((interval + 1) % 7) + 1); + int dayofyear = Date2Julian(year,month,day)-Date2Julian(year,1,1)+1; + weekofmonth = (dayofyear-1+dayofweek-1)/7+1; + } + convertToAscii(weekofmonth,dstDataPtr,2); + return (dstDataPtr - dstData); + } + break; + case DATETIME_FORMAT_EXTRA_J: + { + int julianday = Date2Julian(year,month,day); + convertToAscii(julianday,dstDataPtr,7); + return (dstDataPtr - dstData); + } + break; + case DATETIME_FORMAT_EXTRA_Q: + { + if (month) + { + month = (month-1)/3+1; + } + convertToAscii(month,dstDataPtr,1); + return (dstDataPtr - dstData); + } + break; + default: return -1; } + // Add a delimiter between the date and time portion if required. // if (field > startField && field <= endField) http://git-wip-us.apache.org/repos/asf/trafodion/blob/5c2c5d24/core/sql/exp/exp_datetime.h ---------------------------------------------------------------------- diff --git a/core/sql/exp/exp_datetime.h b/core/sql/exp/exp_datetime.h index 7c3f7a7..3cca57f 100644 --- a/core/sql/exp/exp_datetime.h +++ b/core/sql/exp/exp_datetime.h @@ -44,6 +44,7 @@ #include "exp_attrs.h" #include "Int64.h" +UInt32 Date2Julian(int y, int m ,int d); class ExpDatetime : public SimpleType { @@ -94,9 +95,32 @@ public: DATETIME_FORMAT_NUM2, // -99:99:99:99 DATETIME_FORMAT_MAX_NUM = DATETIME_FORMAT_NUM2, + DATETIME_FORMAT_EXTRA_MIN = DATETIME_FORMAT_MAX_NUM, + DATETIME_FORMAT_EXTRA_HH, // hour of day(00-23) + DATETIME_FORMAT_EXTRA_HH12, // hour of day(01-12) + DATETIME_FORMAT_EXTRA_HH24, // hour of day(00-23) + DATETIME_FORMAT_EXTRA_MI, // minute(00-59) + DATETIME_FORMAT_EXTRA_SS, // second(00-59) + DATETIME_FORMAT_EXTRA_YYYY, // year(4 digits) + DATETIME_FORMAT_EXTRA_YYY, // year(last 3 digits of year) + DATETIME_FORMAT_EXTRA_YY, // year(last 2 digits of year) + DATETIME_FORMAT_EXTRA_Y, // year(last digit of year) + DATETIME_FORMAT_EXTRA_MON, // month(3 chars in English) + DATETIME_FORMAT_EXTRA_MM, // month(01-12) + DATETIME_FORMAT_EXTRA_DY, // name of day(3 chars in English) exp. SUN + DATETIME_FORMAT_EXTRA_DAY, // name of day,padded with blanks to length of 9 characters. exp. SUNDAY + DATETIME_FORMAT_EXTRA_CC, // century + DATETIME_FORMAT_EXTRA_D, // day of week(Sunday(1) to Saturday(7)) + DATETIME_FORMAT_EXTRA_DD, // day of month(01-31) + DATETIME_FORMAT_EXTRA_DDD, // day of year(1-366) + DATETIME_FORMAT_EXTRA_W, // week of month(1-5) + DATETIME_FORMAT_EXTRA_WW, // week number of year(1-53) + DATETIME_FORMAT_EXTRA_J, //number of days since January 1, 4713 BC + DATETIME_FORMAT_EXTRA_Q, // the quarter of year(1-4) + DATETIME_FORMAT_EXTRA_MAX = DATETIME_FORMAT_EXTRA_Q, // the following are intended for binder time resolution based // on operand type to one of the formats above - DATETIME_FORMAT_MIN_UNRESOLVED = DATETIME_FORMAT_MAX_NUM, + DATETIME_FORMAT_MIN_UNRESOLVED = DATETIME_FORMAT_EXTRA_MAX, DATETIME_FORMAT_UNSPECIFIED, // Default format for TO_CHAR; resolved at bind time // based on the datatype of the operand DATETIME_FORMAT_MAX_UNRESOLVED = DATETIME_FORMAT_UNSPECIFIED, @@ -343,6 +367,17 @@ static } } + for (Lng32 i = DATETIME_FORMAT_EXTRA_MIN; i <= DATETIME_FORMAT_EXTRA_MAX; i++) + { + if (stricmp(formatStr, datetimeFormat[i].str) == 0) + { + if (datetimeFormat[i].format != i) + return -1; + + return i; + } + } + for (Lng32 i = DATETIME_FORMAT_MIN_UNRESOLVED; i <= DATETIME_FORMAT_MAX_UNRESOLVED; i++) { if (stricmp(formatStr, datetimeFormat[i].str) == 0) @@ -381,6 +416,12 @@ static (frmt <= DATETIME_FORMAT_MAX_TIME)); } + static NABoolean isExtraFormat(Lng32 frmt) + { + return ((frmt >= DATETIME_FORMAT_EXTRA_MIN) && + (frmt <= DATETIME_FORMAT_EXTRA_MAX)); + } + static NABoolean isNumericFormat(Lng32 frmt) { return ((frmt == DATETIME_FORMAT_NUM1) || (frmt == DATETIME_FORMAT_NUM2)); http://git-wip-us.apache.org/repos/asf/trafodion/blob/5c2c5d24/core/sql/exp/exp_function.cpp ---------------------------------------------------------------------- diff --git a/core/sql/exp/exp_function.cpp b/core/sql/exp/exp_function.cpp index da24f1f..85a5fbf 100644 --- a/core/sql/exp/exp_function.cpp +++ b/core/sql/exp/exp_function.cpp @@ -3007,31 +3007,9 @@ static Int64 lcl_dayofweek(Int64 totaldays) return (unsigned short)((totaldays + 1) % 7) + 1; } -static int lcl_date2Julian(int y, int m ,int d) -{ - int myjulian = 0; - int mycentury = 0; - if ( m <= 2) - { - m = m+13; - y = y+4799; - } - else - { - m = m+1; - y = y+4800; - } - - mycentury = y / 100; - myjulian = y * 365 - 32167; - myjulian += y/4 - mycentury + mycentury / 4; - myjulian += 7834 * m / 256 + d; - return myjulian; -} - static Int64 lcl_dayofyear(char year, char month, char day) { - return (lcl_date2Julian(year,month,day)-lcl_date2Julian(year,1,1)+1); + return (Date2Julian(year,month,day)-Date2Julian(year,1,1)+1); } #define DAYS_PER_YEAR 365.25 /*consider leap year every four years*/ http://git-wip-us.apache.org/repos/asf/trafodion/blob/5c2c5d24/core/sql/optimizer/BindItemExpr.cpp ---------------------------------------------------------------------- diff --git a/core/sql/optimizer/BindItemExpr.cpp b/core/sql/optimizer/BindItemExpr.cpp index 9e84ac4..f8d7b67 100644 --- a/core/sql/optimizer/BindItemExpr.cpp +++ b/core/sql/optimizer/BindItemExpr.cpp @@ -4032,9 +4032,10 @@ NABoolean DateFormat::errorChecks(Lng32 frmt, BindWA *bindWA, NABoolean tf = ExpDatetime::isTimeFormat(frmt); NABoolean tsf = ExpDatetime::isTimestampFormat(frmt); NABoolean nf = ExpDatetime::isNumericFormat(frmt); + NABoolean ef = ExpDatetime::isExtraFormat(frmt); NABoolean ms4 = (CmpCommon::getDefault(MODE_SPECIAL_4) == DF_ON); - if (NOT (df || tf || tsf || nf)) + if (NOT (df || tf || tsf || nf || ef)) { // format must be date, time, timestamp or numeric error = 1; // error 4065 @@ -4309,6 +4310,10 @@ ItemExpr * DateFormat::bindNode(BindWA * bindWA) setChild(0, newChild->bindNode(bindWA)); } } + else if (ExpDatetime::isExtraFormat(frmt_)) + { + dateFormat_ = DateFormat::TIME_FORMAT_STR; + } else { CMPASSERT(FALSE); // should not reach here http://git-wip-us.apache.org/repos/asf/trafodion/blob/5c2c5d24/core/sql/regress/seabase/EXPECTED030 ---------------------------------------------------------------------- diff --git a/core/sql/regress/seabase/EXPECTED030 b/core/sql/regress/seabase/EXPECTED030 index ea32fdf..19fa99e 100644 --- a/core/sql/regress/seabase/EXPECTED030 +++ b/core/sql/regress/seabase/EXPECTED030 @@ -411,6 +411,174 @@ March 01, 2016, 10:11 10:11:12 --- 1 row(s) selected. +>>select to_char(time '23:05:10','HH') from (values(1)) as t(a); + +(EXPR) +------ + +23 + +--- 1 row(s) selected. +>>select to_char(time '23:05:10','HH12') from (values(1)) as t(a); + +(EXPR) +------ + +11 + +--- 1 row(s) selected. +>>select to_char(time '23:05:10','HH24') from (values(1)) as t(a); + +(EXPR) +------ + +23 + +--- 1 row(s) selected. +>>select to_char(time '23:05:10','MI') from (values(1)) as t(a); + +(EXPR) +------ + +05 + +--- 1 row(s) selected. +>>select to_char(time '23:05:10','SS') from (values(1)) as t(a); + +(EXPR) +------ + +10 + +--- 1 row(s) selected. +>>select to_char(timestamp '2016-03-01 12:05:10','YYYY') from (values(1)) as t(a); + +(EXPR) +------ + +2016 + +--- 1 row(s) selected. +>>select to_char(timestamp '2016-03-01 12:05:10','YYY') from (values(1)) as t(a); + +(EXPR) +------ + +016 + +--- 1 row(s) selected. +>>select to_char(timestamp '2016-03-01 12:05:10','YY') from (values(1)) as t(a); + +(EXPR) +------ + +16 + +--- 1 row(s) selected. +>>select to_char(timestamp '2016-03-01 12:05:10','Y') from (values(1)) as t(a); + +(EXPR) +------ + +6 + +--- 1 row(s) selected. +>>select to_char(timestamp '2016-03-01 12:05:10','MON') from (values(1)) as t(a); + +(EXPR) +------ + +MAR + +--- 1 row(s) selected. +>>select to_char(date '2006-12-01','MM') from (values(1)) as t(a); + +(EXPR) +------ + +12 + +--- 1 row(s) selected. +>>select to_char(timestamp '2016-03-01 12:05:10','DY') from (values(1)) as t(a); + +(EXPR) +------ + +TUE + +--- 1 row(s) selected. +>>select to_char(timestamp '2016-03-01 12:05:10','DAY') from (values(1)) as t(a); + +(EXPR) +--------- + +TUESDAY + +--- 1 row(s) selected. +>>select to_char(timestamp '2016-03-01 12:05:10','CC') from (values(1)) as t(a); + +(EXPR) +------ + +21 + +--- 1 row(s) selected. +>>select to_char(timestamp '2016-03-01 12:05:10','D') from (values(1)) as t(a); + +(EXPR) +------ + +3 + +--- 1 row(s) selected. +>>select to_char(date '2006-12-01','DD') from (values(1)) as t(a); + +(EXPR) +------ + +01 + +--- 1 row(s) selected. +>>select to_char(date '2006-12-01','DDD') from (values(1)) as t(a); + +(EXPR) +------ + +335 + +--- 1 row(s) selected. +>>select to_char(date '2006-12-31','W') from (values(1)) as t(a); + +(EXPR) +------ + +5 + +--- 1 row(s) selected. +>>select to_char(date '2006-12-31','WW') from (values(1)) as t(a); + +(EXPR) +------ + +53 + +--- 1 row(s) selected. +>>select to_char(timestamp '2001-01-07 00:00:00','J') from (values(1)) as t(a); + +(EXPR) +------- + +2451917 + +--- 1 row(s) selected. +>>select to_char(date '2006-12-01','Q') from (values(1)) as t(a); + +(EXPR) +------ + +4 + +--- 1 row(s) selected. >> >>select dateformat(time '10:11:12.1', default) from (values(1)) x(a); http://git-wip-us.apache.org/repos/asf/trafodion/blob/5c2c5d24/core/sql/regress/seabase/TEST030 ---------------------------------------------------------------------- diff --git a/core/sql/regress/seabase/TEST030 b/core/sql/regress/seabase/TEST030 index 5a85c5c..03e2145 100644 --- a/core/sql/regress/seabase/TEST030 +++ b/core/sql/regress/seabase/TEST030 @@ -79,6 +79,27 @@ select to_char (time '10:23:34', 'HH:MI:SS') from (values(1)) x(a); select to_char(TIMESTAMP '2016-03-01 10:11:12', 'YYYY-MM-DD') from (values(1)) x(a); select to_char(TIMESTAMP '2016-03-01 10:11:12', 'HH:MI:SS') from (values(1)) x(a); +select to_char(time '23:05:10','HH') from (values(1)) as t(a); +select to_char(time '23:05:10','HH12') from (values(1)) as t(a); +select to_char(time '23:05:10','HH24') from (values(1)) as t(a); +select to_char(time '23:05:10','MI') from (values(1)) as t(a); +select to_char(time '23:05:10','SS') from (values(1)) as t(a); +select to_char(timestamp '2016-03-01 12:05:10','YYYY') from (values(1)) as t(a); +select to_char(timestamp '2016-03-01 12:05:10','YYY') from (values(1)) as t(a); +select to_char(timestamp '2016-03-01 12:05:10','YY') from (values(1)) as t(a); +select to_char(timestamp '2016-03-01 12:05:10','Y') from (values(1)) as t(a); +select to_char(timestamp '2016-03-01 12:05:10','MON') from (values(1)) as t(a); +select to_char(date '2006-12-01','MM') from (values(1)) as t(a); +select to_char(timestamp '2016-03-01 12:05:10','DY') from (values(1)) as t(a); +select to_char(timestamp '2016-03-01 12:05:10','DAY') from (values(1)) as t(a); +select to_char(timestamp '2016-03-01 12:05:10','CC') from (values(1)) as t(a); +select to_char(timestamp '2016-03-01 12:05:10','D') from (values(1)) as t(a); +select to_char(date '2006-12-01','DD') from (values(1)) as t(a); +select to_char(date '2006-12-01','DDD') from (values(1)) as t(a); +select to_char(date '2006-12-31','W') from (values(1)) as t(a); +select to_char(date '2006-12-31','WW') from (values(1)) as t(a); +select to_char(timestamp '2001-01-07 00:00:00','J') from (values(1)) as t(a); +select to_char(date '2006-12-01','Q') from (values(1)) as t(a); select dateformat(time '10:11:12.1', default) from (values(1)) x(a); select dateformat(time '10:11:12.123', usa) from (values(1)) x(a);
