Author: jolee Date: 2012-10-26 10:51:50 -0400 (Fri, 26 Oct 2012) New Revision: 4520 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: TEIID-2246: Incorrect results with TIMESTAMPDIFF 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 2012-10-19 18:42:47 UTC (rev 4519) +++ branches/7.7.x/engine/src/main/java/org/teiid/query/function/FunctionMethods.java 2012-10-26 14:51:50 UTC (rev 4520) @@ -538,32 +538,31 @@ * @throws FunctionExecutionException */ public static Object timestampDiff(String intervalType, Timestamp ts1Obj, Timestamp ts2Obj) { - long ts1 = ts1Obj.getTime() / 1000 * 1000000000 + ts1Obj.getNanos(); - long ts2 = ts2Obj.getTime() / 1000 * 1000000000 + ts2Obj.getNanos(); + long ts1 = ts1Obj.getTime() / 1000; + long ts2 = ts2Obj.getTime() / 1000; long tsDiff = ts2 - ts1; long count = 0; if(intervalType.equalsIgnoreCase(NonReserved.SQL_TSI_FRAC_SECOND)) { - count = tsDiff; + count = tsDiff * 1000000000 + ts2Obj.getNanos() - ts1Obj.getNanos(); } else { - tsDiff = tsDiff / 1000000; //convert to milliseconds if(intervalType.equalsIgnoreCase(NonReserved.SQL_TSI_SECOND)) { - count = tsDiff / 1000; + count = tsDiff; } else if(intervalType.equalsIgnoreCase(NonReserved.SQL_TSI_MINUTE)) { - count = (tsDiff / 1000) / 60; + count = tsDiff / 60; } else if(intervalType.equalsIgnoreCase(NonReserved.SQL_TSI_HOUR)) { - count = (tsDiff / 1000) / (60*60); + count = tsDiff / (60*60); } else if(intervalType.equalsIgnoreCase(NonReserved.SQL_TSI_DAY)) { - count = (tsDiff / 1000) / (60*60*24); + count = tsDiff / (60*60*24); } else if(intervalType.equalsIgnoreCase(NonReserved.SQL_TSI_WEEK)) { - count = (tsDiff / 1000) / (60*60*24*7); + count = tsDiff / (60*60*24*7); } else if(intervalType.equalsIgnoreCase(NonReserved.SQL_TSI_MONTH)) { - count = (tsDiff / 1000) / (60*60*24*30); + count = tsDiff / (60*60*24*30); } else if(intervalType.equalsIgnoreCase(NonReserved.SQL_TSI_QUARTER)) { - count = (tsDiff / 1000) / (60*60*24*91); + count = tsDiff / (60*60*24*91); } else if(intervalType.equalsIgnoreCase(NonReserved.SQL_TSI_YEAR)) { - count = (tsDiff / 1000) / (60*60*24*365); + count = tsDiff / (60*60*24*365); } } return new Long(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 2012-10-19 18:42:47 UTC (rev 4519) +++ branches/7.7.x/engine/src/test/java/org/teiid/query/function/TestFunctionMethods.java 2012-10-26 14:51:50 UTC (rev 4520) @@ -24,7 +24,11 @@ import static org.junit.Assert.*; +import java.sql.Timestamp; import org.junit.Test; +import org.teiid.language.SQLConstants.NonReserved; +import org.teiid.query.unittest.TimestampUtil; +import org.teiid.query.function.FunctionMethods; @SuppressWarnings("nls") public class TestFunctionMethods { @@ -36,5 +40,11 @@ @Test public void testUnescape1() { 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()))); + } }