teiid SVN: r4628 - in branches/7.7.x/engine/src: test/java/org/teiid/query/function and 1 other directory.
by teiid-commits＠lists.jboss.org
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 {
