diff options
3 files changed, 233 insertions, 6 deletions
diff --git a/src/main/java/com/healthmarketscience/jackcess/expr/package-info.java b/src/main/java/com/healthmarketscience/jackcess/expr/package-info.java index a8a697c..048e606 100644 --- a/src/main/java/com/healthmarketscience/jackcess/expr/package-info.java +++ b/src/main/java/com/healthmarketscience/jackcess/expr/package-info.java @@ -133,7 +133,7 @@ limitations under the License. * <tr class="TableRowColor"><td>Day</td><td>Y</td></tr> * <tr class="TableRowColor"><td>Date </td><td>Y</td></tr> * <tr class="TableRowColor"><td>DateAdd</td><td>Y</td></tr> - * <tr class="TableRowColor"><td>DateDiff</td><td></td></tr> + * <tr class="TableRowColor"><td>DateDiff</td><td>Y</td></tr> * <tr class="TableRowColor"><td>DatePart</td><td>Y</td></tr> * <tr class="TableRowColor"><td>DateSerial</td><td>Y</td></tr> * <tr class="TableRowColor"><td>DateValue</td><td>Y</td></tr> diff --git a/src/main/java/com/healthmarketscience/jackcess/impl/expr/DefaultDateFunctions.java b/src/main/java/com/healthmarketscience/jackcess/impl/expr/DefaultDateFunctions.java index 42b037e..cbc2f07 100644 --- a/src/main/java/com/healthmarketscience/jackcess/impl/expr/DefaultDateFunctions.java +++ b/src/main/java/com/healthmarketscience/jackcess/impl/expr/DefaultDateFunctions.java @@ -61,6 +61,7 @@ public class DefaultDateFunctions private static final String INTV_MINUTE = "n"; private static final String INTV_SECOND = "s"; + private enum WeekOpType { GET_WEEK, GET_NUM_WEEKS } private DefaultDateFunctions() {} @@ -128,9 +129,7 @@ public class DefaultDateFunctions if(intv.equalsIgnoreCase(INTV_YEAR)) { result = nonNullToCalendarField(ctx, param2, Calendar.YEAR); } else if(intv.equalsIgnoreCase(INTV_QUARTER)) { - // month in is 0 based - int month = nonNullToCalendarField(ctx, param2, Calendar.MONTH); - result = (month / 3) + 1; + result = getQuarter(nonNullToCalendar(ctx, param2)); } else if(intv.equalsIgnoreCase(INTV_MONTH)) { // convert from 0 based to 1 based value result = nonNullToCalendarField(ctx, param2, Calendar.MONTH) + 1; @@ -198,6 +197,94 @@ public class DefaultDateFunctions } }); + public static final Function DATEDIFF = registerFunc(new FuncVar("DateDiff", 3, 5) { + @Override + protected Value evalVar(EvalContext ctx, Value[] params) { + + Value param2 = params[1]; + Value param3 = params[2]; + if(param2.isNull() || param3.isNull()) { + return ValueSupport.NULL_VAL; + } + + int firstDay = getFirstDayParam(ctx, params, 3); + int firstWeekType = getFirstWeekTypeParam(ctx, params, 4); + + String intv = params[0].getAsString(ctx).trim(); + + Calendar cal1 = nonNullToCalendar(ctx, param2); + Calendar cal2 = nonNullToCalendar(ctx, param3); + + int sign = 1; + if(cal1.after(cal2)) { + Calendar tmp = cal1; + cal1 = cal2; + cal2 = tmp; + sign = -1; + } + + // NOTE: DateDiff understands leap years, but not daylight savings time + // (i.e. it doesn't really take into account timezones). so all time + // based calculations assume 24 hour days. + + int result = -1; + if(intv.equalsIgnoreCase(INTV_YEAR)) { + result = cal2.get(Calendar.YEAR) - cal1.get(Calendar.YEAR); + } else if(intv.equalsIgnoreCase(INTV_QUARTER)) { + int y1 = cal1.get(Calendar.YEAR); + int q1 = getQuarter(cal1); + int y2 = cal2.get(Calendar.YEAR); + int q2 = getQuarter(cal2); + while(y2 > y1) { + q2 += 4; + --y2; + } + result = q2 - q1; + } else if(intv.equalsIgnoreCase(INTV_MONTH)) { + int y1 = cal1.get(Calendar.YEAR); + int m1 = cal1.get(Calendar.MONTH); + int y2 = cal2.get(Calendar.YEAR); + int m2 = cal2.get(Calendar.MONTH); + while(y2 > y1) { + m2 += 12; + --y2; + } + result = m2 - m1; + } else if(intv.equalsIgnoreCase(INTV_DAY_OF_YEAR) || + intv.equalsIgnoreCase(INTV_DAY)) { + result = getDayDiff(cal1, cal2); + } else if(intv.equalsIgnoreCase(INTV_WEEKDAY)) { + // this calulates number of 7 day periods between two dates + result = getDayDiff(cal1, cal2) / 7; + } else if(intv.equalsIgnoreCase(INTV_WEEK)) { + // this counts number of "week of year" intervals between two dates + int w1 = weekOfYear(cal1, firstDay, firstWeekType); + int y1 = getWeekOfYearYear(cal1, w1); + int w2 = weekOfYear(cal2, firstDay, firstWeekType); + int y2 = getWeekOfYearYear(cal2, w2); + while(y2 > y1) { + cal2.add(Calendar.YEAR, -1); + w2 += weeksInYear(cal2, firstDay, firstWeekType); + y2 = cal2.get(Calendar.YEAR); + } + result = w2 - w1; + } else if(intv.equalsIgnoreCase(INTV_HOUR)) { + result = getHourDiff(cal1, cal2); + } else if(intv.equalsIgnoreCase(INTV_MINUTE)) { + result = getMinuteDiff(cal1, cal2); + } else if(intv.equalsIgnoreCase(INTV_SECOND)) { + int s1 = cal1.get(Calendar.SECOND); + int s2 = cal2.get(Calendar.SECOND); + int minuteDiff = getMinuteDiff(cal1, cal2); + result = (s2 + (60 * minuteDiff)) - s1; + } else { + throw new EvalException("Invalid interval " + intv); + } + + return ValueSupport.toValue(result * sign); + } + }); + public static final Function NOW = registerFunc(new Func0("Now") { @Override protected Value eval0(EvalContext ctx) { @@ -466,8 +553,20 @@ public class DefaultDateFunctions private static int weekOfYear(EvalContext ctx, Value param, int firstDay, int firstWeekType) { - Calendar cal = nonNullToCalendar(ctx, param); + return doWeekOp(nonNullToCalendar(ctx, param), firstDay, firstWeekType, + WeekOpType.GET_WEEK); + } + private static int weekOfYear(Calendar cal, int firstDay, int firstWeekType) { + return doWeekOp(cal, firstDay, firstWeekType, WeekOpType.GET_WEEK); + } + + private static int weeksInYear(Calendar cal, int firstDay, int firstWeekType) { + return doWeekOp(cal, firstDay, firstWeekType, WeekOpType.GET_NUM_WEEKS); + } + + private static int doWeekOp(Calendar cal, int firstDay, int firstWeekType, + WeekOpType opType) { // need to mess with some calendar settings, but they need to be restored // when done because the Calendar instance may be shared int origFirstDay = cal.getFirstDayOfWeek(); @@ -496,11 +595,70 @@ public class DefaultDateFunctions cal.setFirstDayOfWeek(firstDay); cal.setMinimalDaysInFirstWeek(minDays); - return cal.get(Calendar.WEEK_OF_YEAR); + switch(opType) { + case GET_WEEK: + return cal.get(Calendar.WEEK_OF_YEAR); + case GET_NUM_WEEKS: + return cal.getActualMaximum(Calendar.WEEK_OF_YEAR); + default: + throw new RuntimeException("Unknown op type " + opType); + } } finally { cal.setFirstDayOfWeek(origFirstDay); cal.setMinimalDaysInFirstWeek(origMinDays); } } + + private static int getQuarter(Calendar cal) { + // month is 0 based + int month = cal.get(Calendar.MONTH); + return (month / 3) + 1; + } + + private static int getWeekOfYearYear(Calendar cal, int weekOfYear) { + // the "week of year" gets weird at the beginning/end of the year. + // e.g. 12/31 might be int the first week of the next year, and 1/1 might + // be in the last week of the previous year. we need to detect this and + // adjust the intervals accordingly + if(cal.get(Calendar.MONTH) == Calendar.JANUARY) { + if(weekOfYear >= 52) { + // this week of year is effectively for the previous year + cal.add(Calendar.YEAR, -1); + } + } else { + if(weekOfYear == 1) { + // this week of year is effectively for next year + cal.add(Calendar.YEAR, 1); + } + } + return cal.get(Calendar.YEAR); + } + + private static int getDayDiff(Calendar cal1, Calendar cal2) { + int y1 = cal1.get(Calendar.YEAR); + int d1 = cal1.get(Calendar.DAY_OF_YEAR); + int y2 = cal2.get(Calendar.YEAR); + int d2 = cal2.get(Calendar.DAY_OF_YEAR); + while(y2 > y1) { + cal2.add(Calendar.YEAR, -1); + d2 += cal2.getActualMaximum(Calendar.DAY_OF_YEAR); + y2 = cal2.get(Calendar.YEAR); + } + return d2 - d1; + } + + private static int getHourDiff(Calendar cal1, Calendar cal2) { + int h1 = cal1.get(Calendar.HOUR_OF_DAY); + int h2 = cal2.get(Calendar.HOUR_OF_DAY); + int dayDiff = getDayDiff(cal1, cal2); + return (h2 + (24 * dayDiff)) - h1; + } + + private static int getMinuteDiff(Calendar cal1, Calendar cal2) { + int m1 = cal1.get(Calendar.MINUTE); + int m2 = cal2.get(Calendar.MINUTE); + int hourDiff = getHourDiff(cal1, cal2); + return (m2 + (60 * hourDiff)) - m1; + } } diff --git a/src/test/java/com/healthmarketscience/jackcess/impl/expr/DefaultFunctionsTest.java b/src/test/java/com/healthmarketscience/jackcess/impl/expr/DefaultFunctionsTest.java index 4b6e388..b9b2657 100644 --- a/src/test/java/com/healthmarketscience/jackcess/impl/expr/DefaultFunctionsTest.java +++ b/src/test/java/com/healthmarketscience/jackcess/impl/expr/DefaultFunctionsTest.java @@ -349,6 +349,75 @@ public class DefaultFunctionsTest extends TestCase assertEquals("11/23/2003", eval("CStr(DateAdd('h',24,#11/22/2003#))")); assertEquals("3:45:13 PM", eval("CStr(DateAdd('h',10,#5:45:13 AM#))")); assertEquals("12/31/1899 11:45:13 AM", eval("CStr(DateAdd('h',30,#5:45:13 AM#))")); + + assertEquals(0, eval("=DateDiff('yyyy',#10/22/2003#,#11/22/2003#)")); + assertEquals(4, eval("=DateDiff('yyyy',#10/22/2003#,#11/22/2007#)")); + assertEquals(-4, eval("=DateDiff('yyyy',#11/22/2007#,#10/22/2003#)")); + + assertEquals(0, eval("=DateDiff('q',#10/22/2003#,#11/22/2003#)")); + assertEquals(3, eval("=DateDiff('q',#03/01/2003#,#11/22/2003#)")); + assertEquals(16, eval("=DateDiff('q',#10/22/2003#,#11/22/2007#)")); + assertEquals(-13, eval("=DateDiff('q',#03/22/2007#,#10/22/2003#)")); + + assertEquals(1, eval("=DateDiff('m',#10/22/2003#,#11/01/2003#)")); + assertEquals(8, eval("=DateDiff('m',#03/22/2003#,#11/01/2003#)")); + assertEquals(49, eval("=DateDiff('m',#10/22/2003#,#11/22/2007#)")); + assertEquals(-41, eval("=DateDiff('m',#03/22/2007#,#10/01/2003#)")); + + assertEquals(10, eval("=DateDiff('d','10/22','11/01')")); + assertEquals(0, eval("=DateDiff('y',#1:37:00 AM#,#2:15:00 AM#)")); + assertEquals(10, eval("=DateDiff('d',#10/22/2003#,#11/01/2003#)")); + assertEquals(1, eval("=DateDiff('d',#10/22/2003 11:00:00 PM#,#10/23/2003 1:00:00 AM#)")); + assertEquals(224, eval("=DateDiff('d',#03/22/2003#,#11/01/2003#)")); + assertEquals(1492, eval("=DateDiff('y',#10/22/2003#,#11/22/2007#)")); + assertEquals(-1268, eval("=DateDiff('d',#03/22/2007#,#10/01/2003#)")); + assertEquals(366, eval("=DateDiff('d',#1/1/2000#,#1/1/2001#)")); + assertEquals(365, eval("=DateDiff('d',#1/1/2001#,#1/1/2002#)")); + + assertEquals(0, eval("=DateDiff('w',#11/3/2018#,#11/04/2018#)")); + assertEquals(1, eval("=DateDiff('w',#11/3/2018#,#11/10/2018#)")); + assertEquals(0, eval("=DateDiff('w',#12/31/2017#,#1/1/2018#)")); + assertEquals(32, eval("=DateDiff('w',#03/22/2003#,#11/01/2003#)")); + assertEquals(213, eval("=DateDiff('w',#10/22/2003#,#11/22/2007#)")); + assertEquals(-181, eval("=DateDiff('w',#03/22/2007#,#10/01/2003#)")); + + assertEquals(1, eval("=DateDiff('ww',#11/3/2018#,#11/04/2018#)")); + assertEquals(1, eval("=DateDiff('ww',#11/3/2018#,#11/10/2018#)")); + assertEquals(0, eval("=DateDiff('ww',#12/31/2017#,#1/1/2018#)")); + assertEquals(1, eval("=DateDiff('ww',#12/31/2017#,#1/1/2018#,2)")); + assertEquals(0, eval("=DateDiff('ww',#12/31/2017#,#1/1/2018#,1,3)")); + assertEquals(53, eval("=DateDiff('ww',#1/1/2000#,#1/1/2001#)")); + assertEquals(32, eval("=DateDiff('ww',#03/22/2003#,#11/01/2003#)")); + assertEquals(213, eval("=DateDiff('ww',#10/22/2003#,#11/22/2007#)")); + assertEquals(-181, eval("=DateDiff('ww',#03/22/2007#,#10/01/2003#)")); + + assertEquals(1, eval("=DateDiff('h',#1:37:00 AM#,#2:15:00 AM#)")); + assertEquals(13, eval("=DateDiff('h',#1:37:00 AM#,#2:15:00 PM#)")); + assertEquals(1, eval("=DateDiff('h',#11/3/2018 1:37:00 AM#,#11/3/2018 2:15:00 AM#)")); + assertEquals(13, eval("=DateDiff('h',#11/3/2018 1:37:00 AM#,#11/3/2018 2:15:00 PM#)")); + assertEquals(24, eval("=DateDiff('h',#11/3/2018#,#11/4/2018#)")); + assertEquals(5641, eval("=DateDiff('h',#3/13/2018 1:37:00 AM#,#11/3/2018 2:15:00 AM#)")); + assertEquals(23161, eval("=DateDiff('h',#3/13/2016 1:37:00 AM#,#11/3/2018 2:15:00 AM#)")); + assertEquals(-23173, eval("=DateDiff('h',#11/3/2018 2:15:00 PM#,#3/13/2016 1:37:00 AM#)")); + + assertEquals(1, eval("=DateDiff('n',#1:37:59 AM#,#1:38:00 AM#)")); + assertEquals(758, eval("=DateDiff('n',#1:37:30 AM#,#2:15:13 PM#)")); + assertEquals(1, eval("=DateDiff('n',#11/3/2018 1:37:59 AM#,#11/3/2018 1:38:00 AM#)")); + assertEquals(758, eval("=DateDiff('n',#11/3/2018 1:37:59 AM#,#11/3/2018 2:15:00 PM#)")); + assertEquals(1440, eval("=DateDiff('n',#11/3/2018#,#11/4/2018#)")); + assertEquals(338438, eval("=DateDiff('n',#3/13/2018 1:37:59 AM#,#11/3/2018 2:15:00 AM#)")); + assertEquals(1389638, eval("=DateDiff('n',#3/13/2016 1:37:30 AM#,#11/3/2018 2:15:13 AM#)")); + assertEquals(-1390358, eval("=DateDiff('n',#11/3/2018 2:15:30 PM#,#3/13/2016 1:37:13 AM#)")); + + assertEquals(1, eval("=DateDiff('s',#1:37:59 AM#,#1:38:00 AM#)")); + assertEquals(35, eval("=DateDiff('s',#1:37:10 AM#,#1:37:45 AM#)")); + assertEquals(45463, eval("=DateDiff('s',#1:37:30 AM#,#2:15:13 PM#)")); + assertEquals(1, eval("=DateDiff('s',#11/3/2018 1:37:59 AM#,#11/3/2018 1:38:00 AM#)")); + assertEquals(45463, eval("=DateDiff('s',#11/3/2018 1:37:30 AM#,#11/3/2018 2:15:13 PM#)")); + assertEquals(86400, eval("=DateDiff('s',#11/3/2018#,#11/4/2018#)")); + assertEquals(20306221, eval("=DateDiff('s',#3/13/2018 1:37:59 AM#,#11/3/2018 2:15:00 AM#)")); + assertEquals(83378263, eval("=DateDiff('s',#3/13/2016 1:37:30 AM#,#11/3/2018 2:15:13 AM#)")); + assertEquals(-83421497, eval("=DateDiff('s',#11/3/2018 2:15:30 PM#,#3/13/2016 1:37:13 AM#)")); } public void testFinancialFuncs() throws Exception |