timestamparith.java

derby database source code.good for you.

/*

   Derby - Class org.apache.derbyTesting.functionTests.tests.lang.timestampArith

   Copyright 2005 The Apache Software Foundation or its licensors, as applicable.

   Licensed under the Apache License, Version 2.0 (the "License");
   you may not use this file except in compliance with the License.
   You may obtain a copy of the License at

      http://www.apache.org/licenses/LICENSE-2.0

   Unless required by applicable law or agreed to in writing, software
   distributed under the License is distributed on an "AS IS" BASIS,
   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
   See the License for the specific language governing permissions and
   limitations under the License.

 */

package org.apache.derbyTesting.functionTests.tests.lang;

import org.apache.derby.tools.ij;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.sql.Types;

import java.util.Calendar;

/**
 * Test the JDBC TIMESTAMPADD and TIMESTAMPDIFF escape functions.
 *
 * Things to test:
 *   + Test each interval type with timestamp, date, and time inputs.
 *   + Test diff with all 9 combinations of datetime input types (timestamp - timestamp, timestamp - date, etc).
 *   + Test PreparedStatements with parameters, '?', in each argument, and Statements. (Statements are prepared
 *     internally so we do not also have to test PrepardStatements without parameters).
 *   + Test with null inputs.
 *   + Test with input string that is convertible to timestamp.
 *   + Test with invalid interval type.
 *   + Test with invalid arguments in the date time arguments.
 *   + Test TIMESTAMPADD with an invalid type in the count argument.
 *   + Test overflow cases.
 */
public class timestampArith
{
    private static final int FRAC_SECOND_INTERVAL = 0;
    private static final int SECOND_INTERVAL = 1;
    private static final int MINUTE_INTERVAL = 2;
    private static final int HOUR_INTERVAL = 3;
    private static final int DAY_INTERVAL = 4;
    private static final int WEEK_INTERVAL = 5;
    private static final int MONTH_INTERVAL = 6;
    private static final int QUARTER_INTERVAL = 7;
    private static final int YEAR_INTERVAL = 8;
    private static final String[] intervalJdbcNames =
    {"SQL_TSI_FRAC_SECOND", "SQL_TSI_SECOND", "SQL_TSI_MINUTE", "SQL_TSI_HOUR",
     "SQL_TSI_DAY", "SQL_TSI_WEEK", "SQL_TSI_MONTH", "SQL_TSI_QUARTER", "SQL_TSI_YEAR"};

    private static final int ONE_BILLION = 1000000000;

    int errorCount = 0;
    private Connection conn;
    private PreparedStatement[] tsAddPS = new PreparedStatement[intervalJdbcNames.length];
    private PreparedStatement[] tsDiffPS = new PreparedStatement[intervalJdbcNames.length];
    private Statement stmt;
    private static final String TODAY;
    private static final String TOMORROW;
    private static final String YEAR_FROM_TOMORROW;
    private static final String YEAR_FROM_TODAY;
    private static final String YESTERDAY;
    private static final String WEEK_FROM_TODAY;
    static {
        Calendar cal = Calendar.getInstance();
        // Make sure that we are not so close to midnight that TODAY might be yesterday before
        // we are finished using it.
        while( cal.get( Calendar.HOUR) == 23 && cal.get( Calendar.MINUTE) == 58)
        {
            try
            {
                Thread.sleep( (60 - cal.get( Calendar.SECOND))*1000);
            }
            catch( InterruptedException ie) {};
            cal = Calendar.getInstance();
        }
        TODAY = isoFormatDate( cal);
        cal.add( Calendar.DATE, -1);
        YESTERDAY = isoFormatDate( cal);
        cal.add( Calendar.DATE, 2);
        TOMORROW = isoFormatDate( cal);
        cal.add( Calendar.YEAR, 1);
        YEAR_FROM_TOMORROW = isoFormatDate( cal);
        cal.add( Calendar.DATE, -1);
        YEAR_FROM_TODAY = isoFormatDate( cal);
        cal.add( Calendar.YEAR, -1); // today
        cal.add( Calendar.DATE, 7);
        WEEK_FROM_TODAY = isoFormatDate( cal);
    }
    
    private static String isoFormatDate( Calendar cal)
    {
        StringBuffer sb = new StringBuffer();
        String s = String.valueOf( cal.get( Calendar.YEAR));
        for( int i = s.length(); i < 4; i++)
            sb.append( '0');
        sb.append( s);
        sb.append( '-');

        s = String.valueOf( cal.get( Calendar.MONTH) + 1);
        for( int i = s.length(); i < 2; i++)
            sb.append( '0');
        sb.append( s);
        sb.append( '-');

        s = String.valueOf( cal.get( Calendar.DAY_OF_MONTH));
        for( int i = s.length(); i < 2; i++)
            sb.append( '0');
        sb.append( s);

        return sb.toString();
    }
    
    private final OneTest[] tests =
    {
        // timestamp - timestamp
        new OneDiffTest( FRAC_SECOND_INTERVAL, ts( "2005-05-10 08:25:00"), ts("2005-05-10 08:25:00.000001"), 1000,
                         null, null),
        new OneDiffTest( SECOND_INTERVAL, ts( "2005-05-10 08:25:01"), ts("2005-05-10 08:25:00"), -1, null, null),
        new OneDiffTest( SECOND_INTERVAL, ts( "2005-05-10 08:25:00.1"), ts("2005-05-10 08:25:00"), 0, null, null),
        new OneDiffTest( SECOND_INTERVAL, ts( "2005-05-10 08:25:00"), ts("2005-05-10 08:26:00"), 60, null, null),
        new OneDiffTest( MINUTE_INTERVAL, ts( "2005-05-11 08:25:00"), ts("2005-05-10 08:25:00"), -24*60, null, null),
        new OneDiffTest( HOUR_INTERVAL, ts("2005-05-10 08:25:00"), ts( "2005-05-11 08:25:00"), 24, null, null),
        new OneDiffTest( DAY_INTERVAL, ts("2005-05-10 08:25:00"), ts( "2005-05-11 08:25:00"), 1, null, null),
        new OneDiffTest( DAY_INTERVAL, ts("2005-05-10 08:25:01"), ts( "2005-05-11 08:25:00"), 0, null, null),
        new OneDiffTest( WEEK_INTERVAL, ts("2005-02-23 08:25:00"), ts( "2005-03-01 08:25:00"), 0, null, null),
        new OneDiffTest( MONTH_INTERVAL, ts("2005-02-23 08:25:00"), ts( "2005-03-23 08:25:00"), 1, null, null),
        new OneDiffTest( MONTH_INTERVAL, ts("2005-02-23 08:25:01"), ts( "2005-03-23 08:25:00"), 0, null, null),
        new OneDiffTest( QUARTER_INTERVAL, ts("2005-02-23 08:25:00"), ts( "2005-05-23 08:25:00"), 1, null, null),
        new OneDiffTest( QUARTER_INTERVAL, ts("2005-02-23 08:25:01"), ts( "2005-05-23 08:25:00"), 0, null, null),
        new OneDiffTest( YEAR_INTERVAL, ts("2005-02-23 08:25:00"), ts( "2005-05-23 08:25:00"), 0, null, null),
        new OneDiffTest( YEAR_INTERVAL, ts("2005-02-23 08:25:00"), ts( "2006-02-23 08:25:00"), 1, null, null),

        // timestamp - time, time - timestamp
        new OneDiffTest( FRAC_SECOND_INTERVAL, ts( TODAY + " 10:00:00.123456"), tm( "10:00:00"), -123456000, null, null),
        new OneDiffTest( FRAC_SECOND_INTERVAL, tm( "10:00:00"), ts( TODAY + " 10:00:00.123456"), 123456000, null, null),
        new OneDiffTest( SECOND_INTERVAL, ts( TODAY + " 10:00:00.1"), tm( "10:00:01"), 0, null, null),
        new OneDiffTest( SECOND_INTERVAL, tm( "10:00:01"), ts( TODAY + " 10:00:00"), -1, null, null),
        new OneDiffTest( MINUTE_INTERVAL, ts( TODAY + " 10:02:00"), tm( "10:00:00"), -2, null, null),
        new OneDiffTest( MINUTE_INTERVAL, tm( "11:00:00"), ts( TODAY + " 10:02:00"), -58, null, null),
        new OneDiffTest( HOUR_INTERVAL, ts( TODAY + " 10:02:00"), tm( "10:00:00"), 0, null, null),
        new OneDiffTest( HOUR_INTERVAL, tm( "10:00:00"), ts( TODAY + " 23:02:00"), 13, null, null),
        new OneDiffTest( DAY_INTERVAL, ts( TODAY + " 00:00:00"), tm( "23:59:59"), 0, null, null),
        new OneDiffTest( DAY_INTERVAL, tm( "23:59:59"), ts( TODAY + " 00:00:00"), 0, null, null),
        new OneDiffTest( WEEK_INTERVAL, ts( TODAY + " 00:00:00"), tm( "23:59:59"), 0, null, null),
        new OneDiffTest( WEEK_INTERVAL, tm( "23:59:59"), ts( TODAY + " 00:00:00"), 0, null, null),
        new OneDiffTest( MONTH_INTERVAL, ts( TODAY + " 00:00:00"), tm( "23:59:59"), 0, null, null),
        new OneDiffTest( MONTH_INTERVAL, tm( "23:59:59"), ts( TODAY + " 00:00:00"), 0, null, null),
        new OneDiffTest( QUARTER_INTERVAL, ts( TODAY + " 00:00:00"), tm( "23:59:59"), 0, null, null),
        new OneDiffTest( QUARTER_INTERVAL, tm( "23:59:59"), ts( TODAY + " 00:00:00"), 0, null, null),
        new OneDiffTest( YEAR_INTERVAL, ts( TODAY + " 00:00:00"), tm( "23:59:59"), 0, null, null),
        new OneDiffTest( YEAR_INTERVAL, tm( "23:59:59"), ts( TODAY + " 00:00:00"), 0, null, null),

        // timestamp - date, date - timestamp
        new OneDiffTest( FRAC_SECOND_INTERVAL, ts( "2004-05-10 00:00:00.123456"), dt("2004-05-10"), -123456000,
                         null, null),
        new OneDiffTest( FRAC_SECOND_INTERVAL, dt("2004-05-10"), ts( "2004-05-10 00:00:00.123456"), 123456000,
                         null, null),
        new OneDiffTest( SECOND_INTERVAL, ts( "2004-05-10 08:25:01"), dt("2004-05-10"), -(1+60*(25+60*8)), null, null),
        new OneDiffTest( SECOND_INTERVAL, dt( "2004-05-10"), ts("2004-05-09 23:59:00"), -60, null, null),
        new OneDiffTest( MINUTE_INTERVAL, ts( "2004-05-11 08:25:00"), dt("2004-05-10"), -(24*60+8*60+25), null, null),
        new OneDiffTest( MINUTE_INTERVAL, dt("2004-05-10"), ts( "2004-05-11 08:25:00"), 24*60+8*60+25, null, null),
        new OneDiffTest( HOUR_INTERVAL, ts("2004-02-28 08:25:00"), dt( "2004-03-01"), 39, null, null),
        new OneDiffTest( HOUR_INTERVAL, dt( "2005-03-01"), ts("2005-02-28 08:25:00"), -15, null, null),
        new OneDiffTest( DAY_INTERVAL, ts("2004-05-10 08:25:00"), dt( "2004-05-11"), 0, null, null),
        new OneDiffTest( DAY_INTERVAL, dt("2004-05-10"), ts( "2004-05-11 08:25:00"), 1, null, null),
        new OneDiffTest( WEEK_INTERVAL, ts("2004-02-23 00:00:00"), dt( "2004-03-01"), 1, null, null),
        new OneDiffTest( WEEK_INTERVAL, dt( "2004-03-01"), ts("2004-02-23 00:00:00"), -1, null, null),
        new OneDiffTest( MONTH_INTERVAL, ts("2004-02-23 08:25:00"), dt( "2004-03-24"), 1, null, null),
        new OneDiffTest( MONTH_INTERVAL, dt( "2005-03-24"), ts("2004-02-23 08:25:00"), -13, null, null),
        new OneDiffTest( QUARTER_INTERVAL, ts("2004-02-23 08:25:00"), dt( "2004-05-24"), 1, null, null),
        new OneDiffTest( QUARTER_INTERVAL, dt( "2004-05-23"), ts("2004-02-23 08:25:01"), 0, null, null),
        new OneDiffTest( YEAR_INTERVAL, ts("2004-02-23 08:25:00"), dt( "2004-05-23"), 0, null, null),
        new OneDiffTest( YEAR_INTERVAL, dt( "2004-05-23"), ts("2003-02-23 08:25:00"), -1, null, null),

        // date - time, time - date
        new OneDiffTest( FRAC_SECOND_INTERVAL, dt( TODAY), tm("00:00:01"), ONE_BILLION, null, null),
        new OneDiffTest( FRAC_SECOND_INTERVAL, tm("00:00:02"), dt( TODAY), -2*ONE_BILLION, null, null),
        new OneDiffTest( SECOND_INTERVAL, dt( TODAY), tm("00:00:01"), 1, null, null),
        new OneDiffTest( SECOND_INTERVAL, tm("00:00:02"), dt( TODAY), -2, null, null),
        new OneDiffTest( MINUTE_INTERVAL, dt( TODAY), tm("12:34:56"), 12*60 + 34, null, null),
        new OneDiffTest( MINUTE_INTERVAL, tm("12:34:56"), dt( TODAY), -(12*60 + 34), null, null),
        new OneDiffTest( HOUR_INTERVAL, dt( TODAY), tm("12:34:56"), 12, null, null),
        new OneDiffTest( HOUR_INTERVAL, tm("12:34:56"), dt( TODAY), -12, null, null),
        new OneDiffTest( DAY_INTERVAL, dt( TOMORROW), tm( "00:00:00"), -1, null, null),
        new OneDiffTest( DAY_INTERVAL, tm( "00:00:00"), dt( TOMORROW), 1, null, null),
        new OneDiffTest( WEEK_INTERVAL, dt( TOMORROW), tm( "00:00:00"), 0, null, null),
        new OneDiffTest( WEEK_INTERVAL, tm( "00:00:00"), dt( TOMORROW), 0, null, null),
        new OneDiffTest( MONTH_INTERVAL, dt( YEAR_FROM_TOMORROW), tm( "12:00:00"), -12, null, null),
        new OneDiffTest( MONTH_INTERVAL, tm( "12:00:00"), dt( YEAR_FROM_TOMORROW), 12, null, null),
        new OneDiffTest( QUARTER_INTERVAL, dt( YEAR_FROM_TOMORROW), tm( "12:00:00"), -4, null, null),
        new OneDiffTest( QUARTER_INTERVAL, tm( "12:00:00"), dt( YEAR_FROM_TOMORROW), 4, null, null),
        new OneDiffTest( YEAR_INTERVAL, dt( YEAR_FROM_TOMORROW), tm( "12:00:00"), -1, null, null),
        new OneDiffTest( YEAR_INTERVAL, tm( "12:00:00"), dt( YEAR_FROM_TOMORROW), 1, null, null),

        // Test add with all combinatons of interval types and datetime types
        new OneAddTest( FRAC_SECOND_INTERVAL, 1000, ts("2005-05-11 15:55:00"), ts("2005-05-11 15:55:00.000001"),
                        null, null),
        new OneAddTest( FRAC_SECOND_INTERVAL, -1000, dt("2005-05-11"), ts("2005-05-10 23:59:59.999999"),
                        null, null),
        new OneAddTest( FRAC_SECOND_INTERVAL, ONE_BILLION, tm("23:59:59"), ts( TOMORROW + " 00:00:00"), null, null),
        new OneAddTest( SECOND_INTERVAL, 60, ts("2005-05-11 15:55:00"), ts("2005-05-11 15:56:00"), null, null),
        new OneAddTest( SECOND_INTERVAL, 60, dt("2005-05-11"), ts("2005-05-11 00:01:00"), null, null),
        new OneAddTest( SECOND_INTERVAL, 60, tm("23:59:30"), ts( TOMORROW + " 00:00:30"), null, null),
        new OneAddTest( MINUTE_INTERVAL, -1, ts("2005-05-11 15:55:00"), ts("2005-05-11 15:54:00"), null, null),
        new OneAddTest( MINUTE_INTERVAL, 1, dt("2005-05-11"), ts("2005-05-11 00:01:00"), null, null),
        new OneAddTest( MINUTE_INTERVAL, 1, tm("12:00:00"), ts( TODAY + " 12:01:00"), null, null),
        new OneAddTest( HOUR_INTERVAL, 2, ts("2005-05-11 15:55:00"), ts("2005-05-11 17:55:00"), null, null),
        new OneAddTest( HOUR_INTERVAL, -2, dt("2005-05-11"), ts("2005-05-10 22:00:00"), null, null),
        new OneAddTest( HOUR_INTERVAL, 1, tm("12:00:00"), ts( TODAY + " 13:00:00"), null, null),
        new OneAddTest( DAY_INTERVAL, 1, ts("2005-05-11 15:55:00"), ts("2005-05-12 15:55:00"), null, null),
        new OneAddTest( DAY_INTERVAL, 1, dt("2005-05-11"), ts("2005-05-12 00:00:00"), null, null),
        new OneAddTest( DAY_INTERVAL, -1, tm( "12:00:00"), ts( YESTERDAY + " 12:00:00"), null, null),
        new OneAddTest( WEEK_INTERVAL, 1, ts("2005-05-11 15:55:00"), ts("2005-05-18 15:55:00"), null, null),
        new OneAddTest( WEEK_INTERVAL, 1, dt("2005-05-11"), ts("2005-05-18 00:00:00"), null, null),
        new OneAddTest( WEEK_INTERVAL, 1, tm("12:00:00"), ts( WEEK_FROM_TODAY + " 12:00:00"), null, null),
        new OneAddTest( MONTH_INTERVAL, 1, ts("2005-05-11 15:55:00"), ts("2005-06-11 15:55:00"), null, null),
        new OneAddTest( MONTH_INTERVAL, -1, dt("2005-03-29"), ts("2005-02-28 00:00:00"), null, null),
        new OneAddTest( MONTH_INTERVAL, 12, tm( "12:00:00"), ts( YEAR_FROM_TODAY + " 12:00:00"), null, null),
        new OneAddTest( QUARTER_INTERVAL, 1, ts("2005-10-11 15:55:00"), ts("2006-01-11 15:55:00"), null, null),
        new OneAddTest( QUARTER_INTERVAL, -2, dt( "2005-05-05"), ts( "2004-11-05 00:00:00"), null, null),
        new OneAddTest( QUARTER_INTERVAL, 4, tm( "12:00:00"), ts( YEAR_FROM_TODAY + " 12:00:00"), null, null),
        new OneAddTest( YEAR_INTERVAL, -10, ts("2005-10-11 15:55:00"), ts("1995-10-11 15:55:00"), null, null),
        new OneAddTest( YEAR_INTERVAL, 2, dt( "2005-05-05"), ts( "2007-05-05 00:00:00"), null, null),
        new OneAddTest( YEAR_INTERVAL, 1, tm( "12:00:00"), ts( YEAR_FROM_TODAY + " 12:00:00"), null, null),

        // String inputs
        new OneStringDiffTest( SECOND_INTERVAL, "2005-05-10 08:25:00", "2005-05-10 08:26:00", 60, null, null),
        new OneStringAddTest( DAY_INTERVAL, 1, "2005-05-11 15:55:00", ts("2005-05-12 15:55:00"), null, null),

        // Overflow
        new OneDiffTest( FRAC_SECOND_INTERVAL, ts( "2004-05-10 00:00:00.123456"), ts( "2004-05-10 00:00:10.123456"), 0,
                         "22003", "The resulting value is outside the range for the data type INTEGER."),
        new OneDiffTest( FRAC_SECOND_INTERVAL, ts( "2004-05-10 00:00:00.123456"), ts( "2005-05-10 00:00:00.123456"), 0,
                         "22003", "The resulting value is outside the range for the data type INTEGER."),
        new OneDiffTest( SECOND_INTERVAL, ts( "1904-05-10 00:00:00"), ts( "2205-05-10 00:00:00"), 0,
                         "22003", "The resulting value is outside the range for the data type INTEGER."),
        new OneAddTest( YEAR_INTERVAL, 99999, ts( "2004-05-10 00:00:00.123456"), null,
                        "22003", "The resulting value is outside the range for the data type TIMESTAMP.")
    };

    private final String[][] invalid =
    {
        {"values( {fn TIMESTAMPDIFF( SECOND, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)})", "42X01",
         "Syntax error: Encountered \"SECOND\" at line 1, column 28."},
        {"values( {fn TIMESTAMPDIFF( , CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)})", "42X01",
         "Syntax error: Encountered \",\" at line 1, column 28."},
        {"values( {fn TIMESTAMPDIFF( SQL_TSI_SECOND, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 5)})", "42X01",
         "Syntax error: Encountered \",\" at line 1, column 80."},
        {"values( {fn TIMESTAMPDIFF( SQL_TSI_SECOND, CURRENT_TIMESTAMP, 'x')})", "42X45",