Author: jolee Date: 2014-04-15 21:51:24 -0400 (Tue, 15 Apr 2014) New Revision: 4628 Modified: branches/7.7.x/engine/src/main/java/org/teiid/query/function/FunctionMethods.java branches/7.7.x/engine/src/test/java/org/teiid/query/function/TestFunctionMethods.java Log: BZ1084108: SQL_TSI_SECOND always returns zero with new implementation of calendar for TIMESTAMPDIFF function Modified: branches/7.7.x/engine/src/main/java/org/teiid/query/function/FunctionMethods.java =================================================================== --- branches/7.7.x/engine/src/main/java/org/teiid/query/function/FunctionMethods.java 2014-02-20 18:30:52 UTC (rev 4627) +++ branches/7.7.x/engine/src/main/java/org/teiid/query/function/FunctionMethods.java 2014-04-16 01:51:24 UTC (rev 4628) @@ -540,9 +540,9 @@ public static Long timestampDiff(String intervalType, Timestamp ts1Obj, Timestamp ts2Obj) throws FunctionExecutionException { return timestampDiff(intervalType, ts1Obj, ts2Obj, CALENDAR_TIMESTAMPDIFF); - } + } - public static Long timestampDiff(String intervalType, Timestamp ts1Obj, Timestamp ts2Obj, boolean calendarBased) throws FunctionExecutionException { + public static Long timestampDiff(String intervalType, Timestamp ts1Obj, Timestamp ts2Obj, boolean calendarBased) throws FunctionExecutionException { long ts1 = ts1Obj.getTime() / 1000; long ts2 = ts2Obj.getTime() / 1000; @@ -551,80 +551,80 @@ long count = 0; if(intervalType.equalsIgnoreCase(NonReserved.SQL_TSI_FRAC_SECOND)) { if (Math.abs(tsDiff) > Integer.MAX_VALUE) { - throw new FunctionExecutionException("TEIID31136", "Value is out of range for timestampdiff"); + throw new FunctionExecutionException("TEIID31136", "Value is out of range for timestampdiff"); } - count = tsDiff * 1000000000 + ts2Obj.getNanos() - ts1Obj.getNanos(); - } else if (calendarBased) { - //alternative logic is needed to compute calendar differences - //which looks at elapsed date parts, not total time between - if(intervalType.equalsIgnoreCase(NonReserved.SQL_TSI_MINUTE)) { - count = ts2 / 60 - ts1 / 60; - } else if(intervalType.equalsIgnoreCase(NonReserved.SQL_TSI_HOUR)) { - TimeZone tz = TimestampWithTimezone.getCalendar().getTimeZone(); - if (tz.getDSTSavings() > 0) { - ts1 += tz.getOffset(ts1Obj.getTime())/1000; - ts2 += tz.getOffset(ts2Obj.getTime())/1000; - } - count = ts2 / (60*60) - ts1 / (60*60); - } else if(intervalType.equalsIgnoreCase(NonReserved.SQL_TSI_DAY) || intervalType.equalsIgnoreCase(NonReserved.SQL_TSI_WEEK)) { - TimeZone tz = TimestampWithTimezone.getCalendar().getTimeZone(); - if (tz.getDSTSavings() > 0) { - ts1 += tz.getOffset(ts1Obj.getTime())/1000; - ts2 += tz.getOffset(ts2Obj.getTime())/1000; - } - //since we are no effectively using GMT we can simply divide since the unix epoch starts at midnight. - count = ts2 / (60*60*24) - ts1 / (60*60*24); - if (intervalType.equalsIgnoreCase(NonReserved.SQL_TSI_WEEK)) { - //TODO: this behavior matches SQL Server - but not Derby which expects only whole week - - long days = count; - //whole weeks between the two dates - count = count/7; - //check for calendar difference assuming sunday as the first week day - if (days%7!=0) { - int day1 = dayOfWeek(ts1Obj); - int day2 = dayOfWeek(ts2Obj); - int diff = Integer.signum(day2 - day1); - if (diff > 0) { - if (tsDiff < 0) { - count--; - } - } else if (diff < 0) { - if (tsDiff > 0) { - count++; - } - } - } - } - } else if(intervalType.equalsIgnoreCase(NonReserved.SQL_TSI_MONTH)) { - Calendar cal = TimestampWithTimezone.getCalendar(); - cal.setTimeInMillis(ts1Obj.getTime()); - int months1 = cal.get(Calendar.YEAR) * 12 + cal.get(Calendar.MONTH); - cal.setTimeInMillis(ts2Obj.getTime()); - int months2 = cal.get(Calendar.YEAR) * 12 + cal.get(Calendar.MONTH); - count = months2 - months1; - } else if(intervalType.equalsIgnoreCase(NonReserved.SQL_TSI_QUARTER)) { - Calendar cal = TimestampWithTimezone.getCalendar(); - cal.setTimeInMillis(ts1Obj.getTime()); - int quarters1 = cal.get(Calendar.YEAR) * 4 + cal.get(Calendar.MONTH)/3; - cal.setTimeInMillis(ts2Obj.getTime()); - int quarters2 = cal.get(Calendar.YEAR) * 4 + cal.get(Calendar.MONTH)/3; - count = quarters2 - quarters1; - } else if(intervalType.equalsIgnoreCase(NonReserved.SQL_TSI_YEAR)) { - Calendar cal = TimestampWithTimezone.getCalendar(); - cal.setTimeInMillis(ts1Obj.getTime()); - int years1 = cal.get(Calendar.YEAR); - cal.setTimeInMillis(ts2Obj.getTime()); - int years2 = cal.get(Calendar.YEAR); - count = years2 - years1; - } + count = tsDiff * 1000000000 + ts2Obj.getNanos() - ts1Obj.getNanos(); } else { if(intervalType.equalsIgnoreCase(NonReserved.SQL_TSI_SECOND)) { count = tsDiff; + } else if (calendarBased) { + //alternative logic is needed to compute calendar differences + //which looks at elapsed date parts, not total time between + if(intervalType.equalsIgnoreCase(NonReserved.SQL_TSI_MINUTE)) { + count = ts2 / 60 - ts1 / 60; + } else if(intervalType.equalsIgnoreCase(NonReserved.SQL_TSI_HOUR)) { + TimeZone tz = TimestampWithTimezone.getCalendar().getTimeZone(); + if (tz.getDSTSavings() > 0) { + ts1 += tz.getOffset(ts1Obj.getTime())/1000; + ts2 += tz.getOffset(ts2Obj.getTime())/1000; + } + count = ts2 / (60*60) - ts1 / (60*60); + } else if(intervalType.equalsIgnoreCase(NonReserved.SQL_TSI_DAY) || intervalType.equalsIgnoreCase(NonReserved.SQL_TSI_WEEK)) { + TimeZone tz = TimestampWithTimezone.getCalendar().getTimeZone(); + if (tz.getDSTSavings() > 0) { + ts1 += tz.getOffset(ts1Obj.getTime())/1000; + ts2 += tz.getOffset(ts2Obj.getTime())/1000; + } + //since we are no effectively using GMT we can simply divide since the unix epoch starts at midnight. + count = ts2 / (60*60*24) - ts1 / (60*60*24); + if (intervalType.equalsIgnoreCase(NonReserved.SQL_TSI_WEEK)) { + //TODO: this behavior matches SQL Server - but not Derby which expects only whole week + + long days = count; + //whole weeks between the two dates + count = count/7; + //check for calendar difference assuming sunday as the first week day + if (days%7!=0) { + int day1 = dayOfWeek(ts1Obj); + int day2 = dayOfWeek(ts2Obj); + int diff = Integer.signum(day2 - day1); + if (diff > 0) { + if (tsDiff < 0) { + count--; + } + } else if (diff < 0) { + if (tsDiff > 0) { + count++; + } + } + } + } + } else if(intervalType.equalsIgnoreCase(NonReserved.SQL_TSI_MONTH)) { + Calendar cal = TimestampWithTimezone.getCalendar(); + cal.setTimeInMillis(ts1Obj.getTime()); + int months1 = cal.get(Calendar.YEAR) * 12 + cal.get(Calendar.MONTH); + cal.setTimeInMillis(ts2Obj.getTime()); + int months2 = cal.get(Calendar.YEAR) * 12 + cal.get(Calendar.MONTH); + count = months2 - months1; + } else if(intervalType.equalsIgnoreCase(NonReserved.SQL_TSI_QUARTER)) { + Calendar cal = TimestampWithTimezone.getCalendar(); + cal.setTimeInMillis(ts1Obj.getTime()); + int quarters1 = cal.get(Calendar.YEAR) * 4 + cal.get(Calendar.MONTH)/3; + cal.setTimeInMillis(ts2Obj.getTime()); + int quarters2 = cal.get(Calendar.YEAR) * 4 + cal.get(Calendar.MONTH)/3; + count = quarters2 - quarters1; + } else if(intervalType.equalsIgnoreCase(NonReserved.SQL_TSI_YEAR)) { + Calendar cal = TimestampWithTimezone.getCalendar(); + cal.setTimeInMillis(ts1Obj.getTime()); + int years1 = cal.get(Calendar.YEAR); + cal.setTimeInMillis(ts2Obj.getTime()); + int years2 = cal.get(Calendar.YEAR); + count = years2 - years1; + } } else if(intervalType.equalsIgnoreCase(NonReserved.SQL_TSI_MINUTE)) { count = tsDiff / 60; } else if(intervalType.equalsIgnoreCase(NonReserved.SQL_TSI_HOUR)) { - count = tsDiff / (60*60); + count = tsDiff / (60*60); } else if(intervalType.equalsIgnoreCase(NonReserved.SQL_TSI_DAY)) { count = tsDiff / (60*60*24); } else if(intervalType.equalsIgnoreCase(NonReserved.SQL_TSI_WEEK)) { @@ -638,9 +638,9 @@ } } //TODO: long results are not consistent with other sources - /*if (calendarBased && ((count > 0 && count > Integer.MAX_VALUE) || (count < 0 && count < Integer.MIN_VALUE))) { - throw new FunctionExecutionException(QueryPlugin.Event.TEIID31136, QueryPlugin.Util.gs(QueryPlugin.Event.TEIID31136)); - }*/ + /*if (calendarBased && ((count > 0 && count > Integer.MAX_VALUE) || (count < 0 && count < Integer.MIN_VALUE))) { + throw new FunctionExecutionException(QueryPlugin.Event.TEIID31136, QueryPlugin.Util.gs(QueryPlugin.Event.TEIID31136)); + }*/ return Long.valueOf(count); } Modified: branches/7.7.x/engine/src/test/java/org/teiid/query/function/TestFunctionMethods.java =================================================================== --- branches/7.7.x/engine/src/test/java/org/teiid/query/function/TestFunctionMethods.java 2014-02-20 18:30:52 UTC (rev 4627) +++ branches/7.7.x/engine/src/test/java/org/teiid/query/function/TestFunctionMethods.java 2014-04-16 01:51:24 UTC (rev 4628) @@ -74,6 +74,12 @@ new Timestamp(TimestampUtil.createDate(404, 3, 13).getTime()), true)); } + @Test public void testTimestampDiffSecond() throws Exception { + assertEquals(Long.valueOf(30), FunctionMethods.timestampDiff(NonReserved.SQL_TSI_SECOND, + new Timestamp(TimestampUtil.createTimestamp(112, 0, 1,0,0,0,0).getTime()), + new Timestamp(TimestampUtil.createTimestamp(112, 0, 1,0,0,30,0).getTime()), true)); + } + @Test public void testTimestampDiffCalendarBasedHour1() throws Exception { TimestampWithTimezone.resetCalendar(TimeZone.getTimeZone("America/New York")); try {