teiid SVN: r4603 - 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: 2013-10-11 10:21:18 -0400 (Fri, 11 Oct 2013)
New Revision: 4603
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/TestFunction.java
branches/7.7.x/engine/src/test/java/org/teiid/query/function/TestFunctionMethods.java
Log:
TEIID-2422: Offer support for a timestampdiff based upon calendar fields
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 2013-10-09 15:43:19 UTC (rev 4602)
+++ branches/7.7.x/engine/src/main/java/org/teiid/query/function/FunctionMethods.java 2013-10-11 14:21:18 UTC (rev 4603)
@@ -55,6 +55,7 @@
import org.teiid.core.types.TransformationException;
import org.teiid.core.types.InputStreamFactory.BlobInputStreamFactory;
import org.teiid.core.types.InputStreamFactory.ClobInputStreamFactory;
+import org.teiid.core.util.PropertiesUtils;
import org.teiid.core.util.TimestampWithTimezone;
import org.teiid.language.SQLConstants;
import org.teiid.language.SQLConstants.NonReserved;
@@ -65,6 +66,8 @@
* Static method hooks for most of the function library.
*/
public final class FunctionMethods {
+
+ private static final boolean CALENDAR_TIMESTAMPDIFF = PropertiesUtils.getBooleanProperty(System.getProperties(), "org.teiid.calendarTimestampDiff", true); //$NON-NLS-1$
// ================== Function = plus =====================
@@ -393,6 +396,8 @@
public static int dayOfWeek(Date x) {
int result = getField(x, Calendar.DAY_OF_WEEK);
if (TimestampWithTimezone.ISO8601_WEEK) {
+ //technically this is just pg indexing
+ //iso would use sunday = 7 rather than 0
return (result + 6) % 7;
}
return result;
@@ -533,15 +538,11 @@
// ================== Function = timestampdiff =====================
- /**
- * This method truncates (ignores) figures
- * @param interval
- * @param timestamp1
- * @param timestamp2
- * @return
- * @throws FunctionExecutionException
- */
- public static Object timestampDiff(String intervalType, Timestamp ts1Obj, Timestamp ts2Obj) {
+ 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 {
long ts1 = ts1Obj.getTime() / 1000;
long ts2 = ts2Obj.getTime() / 1000;
@@ -549,7 +550,74 @@
long count = 0;
if(intervalType.equalsIgnoreCase(NonReserved.SQL_TSI_FRAC_SECOND)) {
- count = tsDiff * 1000000000 + ts2Obj.getNanos() - ts1Obj.getNanos();
+ if (Math.abs(tsDiff) > Integer.MAX_VALUE) {
+ 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;
+ }
} else {
if(intervalType.equalsIgnoreCase(NonReserved.SQL_TSI_SECOND)) {
count = tsDiff;
@@ -569,7 +637,11 @@
count = tsDiff / (60*60*24*365);
}
}
- return new Long(count);
+ //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));
+ }*/
+ return Long.valueOf(count);
}
// ================== Function = timestampcreate =====================
Modified: branches/7.7.x/engine/src/test/java/org/teiid/query/function/TestFunction.java
===================================================================
--- branches/7.7.x/engine/src/test/java/org/teiid/query/function/TestFunction.java 2013-10-09 15:43:19 UTC (rev 4602)
+++ branches/7.7.x/engine/src/test/java/org/teiid/query/function/TestFunction.java 2013-10-11 14:21:18 UTC (rev 4603)
@@ -176,7 +176,7 @@
expected, actual.toString());
}
- public static void helpTestTimestampDiff(String intervalType, Timestamp timeStamp1, Timestamp timeStamp2, Long expected) {
+ public static void helpTestTimestampDiff(String intervalType, Timestamp timeStamp1, Timestamp timeStamp2, Long expected) throws FunctionExecutionException {
Object actual = FunctionMethods.timestampDiff(intervalType, timeStamp1, timeStamp2);
assertEquals("timestampDiff(" + intervalType + ", " + timeStamp1 + ", " + timeStamp2 + ") failed", //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$
expected, actual);
@@ -919,21 +919,21 @@
helpTestTimestampDiff(NonReserved.SQL_TSI_MINUTE,
TimestampUtil.createTimestamp((2001-1900), 0, 0, 2, 0, 0, 0),
TimestampUtil.createTimestamp((2001-1900), 0, 0, 0, 33, 12, 0),
- new Long(-86));
+ new Long(-87));
}
@Test public void testTimestampDiffTimeStamp_Min_3() throws Exception {
helpTestTimestampDiff(NonReserved.SQL_TSI_MINUTE,
TimestampUtil.createTimestamp((2001-1900), 8, 26, 12, 07, 58, 65497),
TimestampUtil.createTimestamp((2001-1900), 8, 29, 11, 25, 42, 483219),
- new Long(4277));
+ new Long(4278));
}
@Test public void testTimestampDiffTimeStamp_Min_4() throws Exception {
helpTestTimestampDiff(NonReserved.SQL_TSI_MINUTE,
TimestampUtil.createTimestamp((2001-1900), 8, 26, 12, 07, 58, 0),
TimestampUtil.createTimestamp((2001-1900), 8, 29, 11, 25, 42, 0),
- new Long(4277));
+ new Long(4278));
}
@Test public void testTimestampDiffTimeStamp_Min_5() throws Exception {
@@ -1024,7 +1024,7 @@
@Test public void testTimestampDiffTime_Hour_2() throws Exception {
helpTestTimestampDiff(NonReserved.SQL_TSI_HOUR, new Timestamp(
TimestampUtil.createTime(5, 0, 30).getTime()), new Timestamp(
- TimestampUtil.createTime(3, 0, 31).getTime()), new Long(-1));
+ TimestampUtil.createTime(3, 0, 31).getTime()), new Long(-2));
}
@Test public void testParseTimestamp1() throws Exception {
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 2013-10-09 15:43:19 UTC (rev 4602)
+++ branches/7.7.x/engine/src/test/java/org/teiid/query/function/TestFunctionMethods.java 2013-10-11 14:21:18 UTC (rev 4603)
@@ -25,7 +25,12 @@
import static org.junit.Assert.*;
import java.sql.Timestamp;
+import java.util.TimeZone;
+
+import org.junit.AfterClass;
+import org.junit.BeforeClass;
import org.junit.Test;
+import org.teiid.core.util.TimestampWithTimezone;
import org.teiid.language.SQLConstants.NonReserved;
import org.teiid.query.unittest.TimestampUtil;
import org.teiid.query.function.FunctionMethods;
@@ -33,6 +38,16 @@
@SuppressWarnings("nls")
public class TestFunctionMethods {
+ @BeforeClass public static void oneTimeSetup() {
+ TimestampWithTimezone.ISO8601_WEEK = true;
+ TimestampWithTimezone.resetCalendar(TimeZone.getTimeZone("GMT-0600"));
+ }
+
+ @AfterClass public static void oneTimeTearDown() {
+ TimestampWithTimezone.ISO8601_WEEK = false;
+ TimestampWithTimezone.resetCalendar(null);
+ }
+
@Test public void testUnescape() {
assertEquals("a\t\n\n%6", FunctionMethods.unescape("a\\t\\n\\012\\456"));
}
@@ -41,10 +56,57 @@
assertEquals("a\u45AA'", FunctionMethods.unescape("a\\u45Aa\'"));
}
- @Test public void testTimestampDiffTimeStamp_ErrorUsingEndDate2304() throws Exception {
- assertEquals(Long.valueOf(106752), FunctionMethods.timestampDiff(NonReserved.SQL_TSI_DAY,
- new Timestamp(TimestampUtil.createDate(112, 0, 1).getTime()),
- new Timestamp(TimestampUtil.createDate(404, 3, 13).getTime())));
- }
+ @Test public void testTimestampDiffTimeStamp_ErrorUsingEndDate2304() throws Exception {
+ assertEquals(Long.valueOf(106753), FunctionMethods.timestampDiff(NonReserved.SQL_TSI_DAY,
+ new Timestamp(TimestampUtil.createDate(112, 0, 1).getTime()),
+ new Timestamp(TimestampUtil.createDate(404, 3, 13).getTime()), false));
+ }
+
+ @Test public void testTimestampDiffTimeStamp_ErrorUsingEndDate2304a() throws Exception {
+ assertEquals(Long.valueOf(32244), FunctionMethods.timestampDiff(NonReserved.SQL_TSI_DAY,
+ new Timestamp(TimestampUtil.createDate(112, 0, 1).getTime()),
+ new Timestamp(TimestampUtil.createDate(200, 3, 13).getTime()), true));
+ }
+
+ @Test public void testTimestampDiffCalendarBasedHour() throws Exception {
+ assertEquals(Long.valueOf(2562072), FunctionMethods.timestampDiff(NonReserved.SQL_TSI_HOUR,
+ new Timestamp(TimestampUtil.createDate(112, 0, 1).getTime()),
+ new Timestamp(TimestampUtil.createDate(404, 3, 13).getTime()), true));
+ }
+
+ @Test public void testTimestampDiffCalendarBasedHour1() throws Exception {
+ TimestampWithTimezone.resetCalendar(TimeZone.getTimeZone("America/New York"));
+ try {
+ assertEquals(Long.valueOf(2472), FunctionMethods.timestampDiff(NonReserved.SQL_TSI_HOUR,
+ new Timestamp(TimestampUtil.createDate(112, 0, 1).getTime()),
+ new Timestamp(TimestampUtil.createDate(112, 3, 13).getTime()), true));
+ } finally {
+ TimestampWithTimezone.resetCalendar(TimeZone.getTimeZone("GMT-0600"));
+ }
+ }
+
+ @Test public void testTimestampDiffCalendarBasedMonth() throws Exception {
+ assertEquals(Long.valueOf(1), FunctionMethods.timestampDiff(NonReserved.SQL_TSI_MONTH,
+ new Timestamp(TimestampUtil.createDate(112, 0, 10).getTime()),
+ new Timestamp(TimestampUtil.createDate(112, 1, 1).getTime()), true));
+ }
+
+ @Test public void testTimestampDiffCalendarBasedWeek() throws Exception {
+ assertEquals(Long.valueOf(1), FunctionMethods.timestampDiff(NonReserved.SQL_TSI_WEEK,
+ new Timestamp(TimestampUtil.createDate(113, 2, 2).getTime()),
+ new Timestamp(TimestampUtil.createDate(113, 2, 3).getTime()), true));
+ }
+
+ @Test public void testTimestampDiffCalendarBasedWeek1() throws Exception {
+ assertEquals(Long.valueOf(0), FunctionMethods.timestampDiff(NonReserved.SQL_TSI_WEEK,
+ new Timestamp(TimestampUtil.createDate(113, 2, 3).getTime()),
+ new Timestamp(TimestampUtil.createDate(113, 2, 4).getTime()), true));
+ }
+
+ @Test public void testTimestampDiffCalendarBasedWeek2() throws Exception {
+ assertEquals(Long.valueOf(0), FunctionMethods.timestampDiff(NonReserved.SQL_TSI_WEEK,
+ new Timestamp(TimestampUtil.createDate(113, 2, 4).getTime()),
+ new Timestamp(TimestampUtil.createDate(113, 2, 3).getTime()), true));
+ }
}
