Java Code Examples for java.sql.ResultSet#getDate()

@Test
public void nullInDateParameter() throws Exception {
    Connection conn = DriverManager.getConnection(getUrl());
    String timezone_offset_test = generateUniqueName();
    String ddl = "CREATE TABLE IF NOT EXISTS " + timezone_offset_test
        + " (k1 INTEGER NOT NULL, dates DATE CONSTRAINT pk PRIMARY KEY (k1))";
    conn.createStatement().execute(ddl);
    String dml = "UPSERT INTO " + timezone_offset_test + " (k1) VALUES (1)";
    conn.createStatement().execute(dml);
    conn.commit();

    ResultSet rs = conn.createStatement().executeQuery(
        "SELECT k1, dates, CONVERT_TZ(dates, 'UTC', 'America/Adak') FROM "
            + timezone_offset_test);

    assertTrue(rs.next());
    rs.getDate(3);
    assertTrue(rs.wasNull());
}
 

@Test
public void testRoundAggregation() throws Exception {
    long ts = nextTimestamp();
    String tenantId = getOrganizationId();
    String query = "SELECT round(date,'hour',1) r,count(1) FROM PRODUCT_METRICS WHERE organization_id=? GROUP BY r";
    String url = getUrl() + ";" + PhoenixRuntime.CURRENT_SCN_ATTRIB + "=" + (ts + 5); // Run query at timestamp 5
    Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
    Connection conn = DriverManager.getConnection(url, props);
    try {
        initTableValues(tenantId, getSplits(tenantId), ts);
        PreparedStatement statement = conn.prepareStatement(query);
        statement.setString(1, tenantId);
        Date d;
        int c;
        ResultSet rs = statement.executeQuery();
        
        assertTrue(rs.next());
        d = rs.getDate(1);
        c = rs.getInt(2);
        assertEquals(1 * 60 * 60 * 1000, d.getTime()); // Date bucketed into 1 hr
        assertEquals(2, c);
        
        assertTrue(rs.next());
        d = rs.getDate(1);
        c = rs.getInt(2);
        assertEquals(2 * 60 * 60 * 1000, d.getTime()); // Date bucketed into 2 hr
        assertEquals(3, c);
        
        assertTrue(rs.next());
        d = rs.getDate(1);
        c = rs.getInt(2);
        assertEquals(4 * 60 * 60 * 1000, d.getTime()); // Date bucketed into 4 hr
        assertEquals(1, c);
        
        assertFalse(rs.next());
    } finally {
        conn.close();
    }
}
 

/**
 * Retrieve a JDBC column value from a ResultSet, using the most appropriate
 * value type. The returned value should be a detached value object, not having
 * any ties to the active ResultSet: in particular, it should not be a Blob or
 * Clob object but rather a byte array or String representation, respectively.
 * <p>Uses the {@code getObject(index)} method, but includes additional "hacks"
 * to get around Oracle 10g returning a non-standard object for its TIMESTAMP
 * datatype and a {@code java.sql.Date} for DATE columns leaving out the
 * time portion: These columns will explicitly be extracted as standard
 * {@code java.sql.Timestamp} object.
 * @param rs is the ResultSet holding the data
 * @param index is the column index
 * @return the value object
 * @throws SQLException if thrown by the JDBC API
 * @see java.sql.Blob
 * @see java.sql.Clob
 * @see java.sql.Timestamp
 */
public static Object getResultSetValue(ResultSet rs, int index) throws SQLException {
	Object obj = rs.getObject(index);
	String className = null;
	if (obj != null) {
		className = obj.getClass().getName();
	}
	if (obj instanceof Blob) {
		Blob blob = (Blob) obj;
		obj = blob.getBytes(1, (int) blob.length());
	}
	else if (obj instanceof Clob) {
		Clob clob = (Clob) obj;
		obj = clob.getSubString(1, (int) clob.length());
	}
	else if ("oracle.sql.TIMESTAMP".equals(className) || "oracle.sql.TIMESTAMPTZ".equals(className)) {
		obj = rs.getTimestamp(index);
	}
	else if (className != null && className.startsWith("oracle.sql.DATE")) {
		String metaDataClassName = rs.getMetaData().getColumnClassName(index);
		if ("java.sql.Timestamp".equals(metaDataClassName) || "oracle.sql.TIMESTAMP".equals(metaDataClassName)) {
			obj = rs.getTimestamp(index);
		}
		else {
			obj = rs.getDate(index);
		}
	}
	else if (obj instanceof java.sql.Date) {
		if ("java.sql.Timestamp".equals(rs.getMetaData().getColumnClassName(index))) {
			obj = rs.getTimestamp(index);
		}
	}
	return obj;
}
 

/**
 * Retrieve a JDBC column value from a ResultSet, using the most appropriate
 * value type. The returned value should be a detached value object, not having
 * any ties to the active ResultSet: in particular, it should not be a Blob or
 * Clob object but rather a byte array or String representation, respectively.
 * <p>Uses the {@code getObject(index)} method, but includes additional "hacks"
 * to get around Oracle 10g returning a non-standard object for its TIMESTAMP
 * datatype and a {@code java.sql.Date} for DATE columns leaving out the
 * time portion: These columns will explicitly be extracted as standard
 * {@code java.sql.Timestamp} object.
 *
 * @param rs    is the ResultSet holding the data
 * @param index is the column index
 * @return the value object
 * @throws SQLException if thrown by the JDBC API
 * @see java.sql.Blob
 * @see java.sql.Clob
 * @see java.sql.Timestamp
 */
@Nullable
public static Object getResultSetValue(ResultSet rs, int index) throws SQLException {
    Object obj = rs.getObject(index);
    String className = null;
    if (obj != null) {
        className = obj.getClass().getName();
    }
    if (obj instanceof Blob) {
        Blob blob = (Blob) obj;
        obj = blob.getBytes(1, (int) blob.length());
    } else if (obj instanceof Clob) {
        Clob clob = (Clob) obj;
        obj = clob.getSubString(1, (int) clob.length());
    } else if ("oracle.sql.TIMESTAMP".equals(className) || "oracle.sql.TIMESTAMPTZ".equals(className)) {
        obj = rs.getTimestamp(index);
    } else if (className != null && className.startsWith("oracle.sql.DATE")) {
        String metaDataClassName = rs.getMetaData().getColumnClassName(index);
        if ("java.sql.Timestamp".equals(metaDataClassName) || "oracle.sql.TIMESTAMP".equals(metaDataClassName)) {
            obj = rs.getTimestamp(index);
        } else {
            obj = rs.getDate(index);
        }
    } else if (obj instanceof java.sql.Date) {
        if ("java.sql.Timestamp".equals(rs.getMetaData().getColumnClassName(index))) {
            obj = rs.getTimestamp(index);
        }
    }
    return obj;
}
 

public Spittle mapRow(ResultSet rs, int rowNum) throws SQLException {
  return new Spittle(
      rs.getLong("id"),
      rs.getString("message"), 
      rs.getDate("created_at"), 
      rs.getDouble("longitude"), 
      rs.getDouble("latitude"));
}
 

@Test
public void testCurrentDate() throws Exception {
    String transTableName = generateUniqueName();
    String fullTableName = INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + transTableName;
    String selectSql = "SELECT current_date() FROM "+fullTableName;
    try (Connection conn = DriverManager.getConnection(getUrl())) {
        conn.createStatement().execute("create table " + fullTableName + TestUtil.TEST_TABLE_SCHEMA + tableDDLOptions + (tableDDLOptions.length() > 0 ? "," : "") + "TRANSACTIONAL=true");
        conn.setAutoCommit(false);
        ResultSet rs = conn.createStatement().executeQuery(selectSql);
        assertFalse(rs.next());
        
        String upsert = "UPSERT INTO " + fullTableName + "(varchar_pk, char_pk, int_pk, long_pk, decimal_pk, date_pk) VALUES(?, ?, ?, ?, ?, ?)";
        PreparedStatement stmt = conn.prepareStatement(upsert);
        // upsert two rows
        TestUtil.setRowKeyColumns(stmt, 1);
        stmt.execute();
        conn.commit();
        
        rs = conn.createStatement().executeQuery(selectSql);
        assertTrue(rs.next());
        Date date1 = rs.getDate(1);
        assertFalse(rs.next());
        
        Thread.sleep(1000);
        
        rs = conn.createStatement().executeQuery(selectSql);
        assertTrue(rs.next());
        Date date2 = rs.getDate(1);
        assertFalse(rs.next());
        assertTrue("current_date() should change while executing multiple statements", date2.getTime() > date1.getTime());
    }
}
 

/**
 *  Sets the Jetel attribute of the CopyDate object
 *
 * @param  resultSet         The new Jetel value
 * @exception  SQLException  Description of Exception
 * @since                    October 7, 2002
 */
@Override
public void setJetel(ResultSet resultSet) throws SQLException {
	Date date = resultSet.getDate(fieldSQL);
	if (resultSet.wasNull()) {
		((DateDataField) field).setValue((Object)null);
	}else{
		((DateDataField) field).setValue(date);
	}
	
}
 

/**
 * Retrieve a JDBC column value from a ResultSet, using the most appropriate
 * value type. The returned value should be a detached value object, not having
 * any ties to the active ResultSet: in particular, it should not be a Blob or
 * Clob object but rather a byte array or String representation, respectively.
 * <p>Uses the {@code getObject(index)} method, but includes additional "hacks"
 * to get around Oracle 10g returning a non-standard object for its TIMESTAMP
 * datatype and a {@code java.sql.Date} for DATE columns leaving out the
 * time portion: These columns will explicitly be extracted as standard
 * {@code java.sql.Timestamp} object.
 * @param rs is the ResultSet holding the data
 * @param index is the column index
 * @return the value object
 * @throws SQLException if thrown by the JDBC API
 * @see java.sql.Blob
 * @see java.sql.Clob
 * @see java.sql.Timestamp
 */
@Nullable
public static Object getResultSetValue(ResultSet rs, int index) throws SQLException {
	Object obj = rs.getObject(index);
	String className = null;
	if (obj != null) {
		className = obj.getClass().getName();
	}
	if (obj instanceof Blob) {
		Blob blob = (Blob) obj;
		obj = blob.getBytes(1, (int) blob.length());
	}
	else if (obj instanceof Clob) {
		Clob clob = (Clob) obj;
		obj = clob.getSubString(1, (int) clob.length());
	}
	else if ("oracle.sql.TIMESTAMP".equals(className) || "oracle.sql.TIMESTAMPTZ".equals(className)) {
		obj = rs.getTimestamp(index);
	}
	else if (className != null && className.startsWith("oracle.sql.DATE")) {
		String metaDataClassName = rs.getMetaData().getColumnClassName(index);
		if ("java.sql.Timestamp".equals(metaDataClassName) || "oracle.sql.TIMESTAMP".equals(metaDataClassName)) {
			obj = rs.getTimestamp(index);
		}
		else {
			obj = rs.getDate(index);
		}
	}
	else if (obj instanceof java.sql.Date) {
		if ("java.sql.Timestamp".equals(rs.getMetaData().getColumnClassName(index))) {
			obj = rs.getTimestamp(index);
		}
	}
	return obj;
}
 

@Override
public Date getNullableResult(ResultSet rs, int columnIndex)
    throws SQLException {
  java.sql.Date sqlDate = rs.getDate(columnIndex);
  if (sqlDate != null) {
    return new java.util.Date(sqlDate.getTime());
  }
  return null;
}
 

private DataValueDto getValueAsDataSourceValueDateWeb(ResultSet row, String columnName) throws SQLException {
    Date value = row.getDate(row.findColumn(columnName));
    return new DataValueDto<Date>()
            .setValue(value);
}
 

@Override
public LocalDate handleColumn(ResultSet resultSet, int column) throws SQLException {
    var date = resultSet.getDate(column);
    return  nonNull(date) ? date.toLocalDate() : null;
}
 

private static Object getValue(ResultSet resultSet, int type, int j,
    Calendar calendar) throws SQLException {
  switch (type) {
  case Types.BIGINT:
    final long aLong = resultSet.getLong(j + 1);
    return aLong == 0 && resultSet.wasNull() ? null : aLong;
  case Types.INTEGER:
    final int anInt = resultSet.getInt(j + 1);
    return anInt == 0 && resultSet.wasNull() ? null : anInt;
  case Types.SMALLINT:
    final short aShort = resultSet.getShort(j + 1);
    return aShort == 0 && resultSet.wasNull() ? null : aShort;
  case Types.TINYINT:
    final byte aByte = resultSet.getByte(j + 1);
    return aByte == 0 && resultSet.wasNull() ? null : aByte;
  case Types.DOUBLE:
  case Types.FLOAT:
    final double aDouble = resultSet.getDouble(j + 1);
    return aDouble == 0D && resultSet.wasNull() ? null : aDouble;
  case Types.REAL:
    final float aFloat = resultSet.getFloat(j + 1);
    return aFloat == 0D && resultSet.wasNull() ? null : aFloat;
  case Types.DATE:
    final Date aDate = resultSet.getDate(j + 1, calendar);
    return aDate == null
        ? null
        : (int) (aDate.getTime() / DateTimeUtils.MILLIS_PER_DAY);
  case Types.TIME:
    final Time aTime = resultSet.getTime(j + 1, calendar);
    return aTime == null
        ? null
        : (int) (aTime.getTime() % DateTimeUtils.MILLIS_PER_DAY);
  case Types.TIMESTAMP:
    final Timestamp aTimestamp = resultSet.getTimestamp(j + 1, calendar);
    return aTimestamp == null ? null : aTimestamp.getTime();
  case Types.ARRAY:
    final Array array = resultSet.getArray(j + 1);
    if (null == array) {
      return null;
    }
    try {
      // Recursively extracts an Array using its ResultSet-representation
      return extractUsingResultSet(array, calendar);
    } catch (UnsupportedOperationException | SQLFeatureNotSupportedException e) {
      // Not every database might implement Array.getResultSet(). This call
      // assumes a non-nested array (depends on the db if that's a valid assumption)
      return extractUsingArray(array, calendar);
    }
  case Types.STRUCT:
    Struct struct = resultSet.getObject(j + 1, Struct.class);
    Object[] attrs = struct.getAttributes();
    List<Object> list = new ArrayList<>(attrs.length);
    for (Object o : attrs) {
      list.add(o);
    }
    return list;
  default:
    return resultSet.getObject(j + 1);
  }
}
 

/**
 * Get a value from a result set column based on the current value metadata
 *
 * @param databaseInterface
 *          the database metadata to use
 * @param resultSet
 *          The JDBC result set to read from
 * @param index
 *          The column index (1-based)
 * @return The Kettle native data type based on the value metadata
 * @throws KettleDatabaseException
 *           in case something goes wrong.
 */
@Override
public Object getValueFromResultSet( DatabaseInterface databaseInterface, ResultSet resultSet, int index )
  throws KettleDatabaseException {
  try {
    Object data = null;

    switch ( getType() ) {
      case ValueMetaInterface.TYPE_BOOLEAN:
        data = Boolean.valueOf( resultSet.getBoolean( index + 1 ) );
        break;
      case ValueMetaInterface.TYPE_NUMBER:
        data = new Double( resultSet.getDouble( index + 1 ) );
        break;
      case ValueMetaInterface.TYPE_BIGNUMBER:
        data = resultSet.getBigDecimal( index + 1 );
        break;
      case ValueMetaInterface.TYPE_INTEGER:
        data = Long.valueOf( resultSet.getLong( index + 1 ) );
        break;
      case ValueMetaInterface.TYPE_STRING:
        if ( isStorageBinaryString() ) {
          data = resultSet.getBytes( index + 1 );
        } else {
          data = resultSet.getString( index + 1 );
        }
        break;
      case ValueMetaInterface.TYPE_BINARY:
        if ( databaseInterface.supportsGetBlob() ) {
          Blob blob = resultSet.getBlob( index + 1 );
          if ( blob != null ) {
            data = blob.getBytes( 1L, (int) blob.length() );
          } else {
            data = null;
          }
        } else {
          data = resultSet.getBytes( index + 1 );
        }
        break;

      case ValueMetaInterface.TYPE_DATE:
        if ( getPrecision() != 1 && databaseInterface.supportsTimeStampToDateConversion() ) {
          data = resultSet.getTimestamp( index + 1 );
          break; // Timestamp extends java.util.Date
        } else if ( databaseInterface instanceof NetezzaDatabaseMeta ) {
          // PDI-10877 workaround for IBM netezza jdbc 'special' implementation
          data = getNetezzaDateValueWorkaround( databaseInterface, resultSet, index + 1 );
          break;
        } else {
          data = resultSet.getDate( index + 1 );
          break;
        }
      default:
        break;
    }
    if ( resultSet.wasNull() ) {
      data = null;
    }
    return data;
  } catch ( SQLException e ) {
    throw new KettleDatabaseException( "Unable to get value '" + toStringMeta() + "' from database resultset, index "
        + index, e );
  }

}
 

@Test
public void testNewAndUpdateOnSingleNumericColumnWithOtherColumns() throws Exception {
    Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
    Connection conn = DriverManager.getConnection(getUrl(), props);
    String tableName = generateUniqueName();
    String ddl = " create table " + tableName + "(k1 varchar, k2 varchar, counter1 varchar, counter2 date, other1 char(3), other2 varchar default 'f', constraint pk primary key (k1,k2))";
    conn.createStatement().execute(ddl);
    createIndex(conn, tableName);
    String dml = "UPSERT INTO " + tableName + " VALUES('a','b','c',null,'eee') " + 
                 "ON DUPLICATE KEY UPDATE counter1 = counter1 || CASE WHEN LENGTH(counter1) < 10 THEN 'SMALL' ELSE 'LARGE' END || k2 || other2 || other1 ";
    conn.createStatement().execute(dml);
    conn.commit();

    ResultSet rs = conn.createStatement().executeQuery("SELECT * FROM " + tableName);
    assertTrue(rs.next());
    assertEquals("a",rs.getString(1));
    assertEquals("b",rs.getString(2));
    assertEquals("c",rs.getString(3));
    assertEquals(null,rs.getDate(4));
    assertEquals("eee",rs.getString(5));
    assertEquals("f",rs.getString(6));
    assertFalse(rs.next());
    
    conn.createStatement().execute(dml);
    conn.commit();

    rs = conn.createStatement().executeQuery("SELECT * FROM " + tableName);
    assertTrue(rs.next());
    assertEquals("a",rs.getString(1));
    assertEquals("b",rs.getString(2));
    assertEquals("cSMALLbfeee",rs.getString(3));
    assertEquals(null,rs.getDate(4));
    assertEquals("eee",rs.getString(5));
    assertEquals("f",rs.getString(6));
    assertFalse(rs.next());
    
    conn.createStatement().execute(dml);
    conn.commit();

    rs = conn.createStatement().executeQuery("SELECT * FROM " + tableName);
    assertTrue(rs.next());
    assertEquals("a",rs.getString(1));
    assertEquals("b",rs.getString(2));
    assertEquals("cSMALLbfeeeLARGEbfeee",rs.getString(3));
    assertEquals(null,rs.getDate(4));
    assertEquals("eee",rs.getString(5));
    assertEquals("f",rs.getString(6));
    assertFalse(rs.next());
    
    conn.createStatement().execute("UPSERT INTO " + tableName + " VALUES('a','b','c',null,'eee') " + 
            "ON DUPLICATE KEY UPDATE counter1 = to_char(rand()), counter2 = current_date() + 1");
    conn.commit();

    rs = conn.createStatement().executeQuery("SELECT * FROM " + tableName);
    assertTrue(rs.next());
    assertEquals("a",rs.getString(1));
    assertEquals("b",rs.getString(2));
    double d = Double.parseDouble(rs.getString(3));
    assertTrue(d >= 0.0 && d <= 1.0);
    Date date = rs.getDate(4);
    assertTrue(date.after(new Date(System.currentTimeMillis())));
    assertEquals("eee",rs.getString(5));
    assertEquals("f",rs.getString(6));
    assertFalse(rs.next());
    
    conn.close();
}
 

public static Date readDate(int colNum, ResultSet r) throws SQLException {
  return r.getDate(colNum);
}
 

private void _testQueryMoreFunctionalityUsingAllPkColsInRowValueConstructor(boolean salted) throws Exception, SQLException {
    String tenantId = getOrganizationId();
    Date date = new Date(System.currentTimeMillis());
    String tableName;
    if(salted) {
        tableName = initSaltedEntityHistoryTableValues(null, tenantId, null, date, null);
    } else {
        tableName = initEntityHistoryTableValues(null, tenantId, getDefaultSplits(tenantId), date, null);
    }
    Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
    Connection conn = DriverManager.getConnection(getUrl(), props);
    
    String startingOrgId = tenantId;
    String startingParentId = PARENTID1;
    Date startingDate = date;
    String startingEntityHistId = ENTITYHISTID1;
    PreparedStatement statement = null;
    statement = conn.prepareStatement("select organization_id, parent_id, created_date, entity_history_id, old_value, new_value from " + tableName + 
            " WHERE (organization_id, parent_id, created_date, entity_history_id) > (?, ?, ?, ?) ORDER BY organization_id, parent_id, created_date, entity_history_id LIMIT 3 ");
    statement.setString(1, startingOrgId);
    statement.setString(2, startingParentId);
    statement.setDate(3, startingDate);
    statement.setString(4, startingEntityHistId);
    ResultSet rs = statement.executeQuery();

    int count = 0;
    int i = 1;
    //this loop should work on rows 2, 3, 4.
    while(rs.next()) {
        assertTrue(rs.getString(2).equals(PARENTIDS.get(i)));
        assertTrue(rs.getString(4).equals(ENTITYHISTIDS.get(i)));
        count++;
        i++;
        if(count == 3) {
            startingOrgId = rs.getString(1);
            startingParentId = rs.getString(2);
            startingDate = rs.getDate(3);
            startingEntityHistId = rs.getString(4);
        }
    }

    assertTrue("Number of rows returned: ", count == 3);
    //We will now use the row 4's pk values for bind variables. 
    statement.setString(1, startingOrgId);
    statement.setString(2, startingParentId);
    statement.setDate(3, startingDate);
    statement.setString(4, startingEntityHistId);
    rs = statement.executeQuery();
    //this loop now should work on rows 5, 6, 7.
    while(rs.next()) {
        assertTrue(rs.getString(2).equals(PARENTIDS.get(i)));
        assertTrue(rs.getString(4).equals(ENTITYHISTIDS.get(i)));
        i++;
        count++;
    }
    assertTrue("Number of rows returned: ", count == 6);
}
 

@Override
public Date getNullableResult(ResultSet rs, String columnName)
    throws SQLException {
  return rs.getDate(columnName);
}
 

/**
 * Check the consistency of a ResultSet column that returns
 * a TIME value. Can be used for any column of type TIME.
 * 
 * @param rs ResultSet holding the column, positioned on a row
 * @param column Column with the TIME value.
 * @return Returns the Time object obtained from the column.
 * @throws SQLException
 */
private Time checkTimeValue(ResultSet rs, int column) throws SQLException
{
    assertEquals(java.sql.Types.TIME, rs.getMetaData().getColumnType(column));
    
    try {
        rs.getDate(column);
        fail("ResultSet.getDate() succeeded on TIME column");
    } catch (SQLException e) {
        assertSQLState("22005", e);
    }
  
    Time tv = rs.getTime(column);
    assertEquals(tv == null, rs.wasNull());
    
    Object ov = rs.getObject(column);
    assertEquals(ov == null, rs.wasNull());
    
    if (tv == null) {
        assertNull(ov);
        return null;
    }
    
    assertTrue(ov instanceof java.sql.Time);
    assertEquals(tv, ov);
     
    // Check the date portion is set to 1970/01/01
    assertTime1970(tv);
    cal.clear();
    cal.setTime(tv);
    
    // Check the milli-seconds portion is 0
    // Derby does not support that precision in TIME
    assertEquals(0, cal.get(Calendar.MILLISECOND));
    
    long now = System.currentTimeMillis();
    Timestamp tsv = rs.getTimestamp(column);
    long now2 = System.currentTimeMillis();
    assertNotNull(tsv);
    assertFalse(rs.wasNull());
    
    // Check the TIME portion is set to the same as tv
    assertTimeEqual(tv, tsv);
    
    // DERBY-1811, DERBY-889 being fixed could add tests
    // Check the returned date portion is the current date
    // using the value from 'now' and 'now2'. Double check
    // just in case this test runs at midnight.
    if (!(isDateEqual(now, tsv) || isDateEqual(now2, tsv)))
    {
        fail("TIME to java.sql.Timestamp does not contain current date " + tsv);
    }
    
    String sv = rs.getString(column);
    assertNotNull(sv);
    assertFalse(rs.wasNull());
    
    // Assert the string converted back into a Time matches the Time returned.
    assertEquals("ResultSet String converted to java.sql.Time mismatch",
            tv, getTime19700101(sv, cal));
    
    return tv;
}
 

@Test
public void testTruncAggregation() throws Exception {
    long ts = nextTimestamp();
    String tenantId = getOrganizationId();
    String query = "SELECT trunc(date,'hour'),count(1) FROM PRODUCT_METRICS WHERE organization_id=? GROUP BY trunc(date,'hour')";
    String url = PHOENIX_JDBC_URL + ";" + PhoenixRuntime.CURRENT_SCN_ATTRIB + "=" + (ts + 5); // Run query at timestamp 5
    Properties props = new Properties(TEST_PROPERTIES);
    Connection conn = DriverManager.getConnection(url, props);
    try {
        initTableValues(tenantId, getSplits(tenantId), ts);
        PreparedStatement statement = conn.prepareStatement(query);
        statement.setString(1, tenantId);
        Date d;
        int c;
        ResultSet rs = statement.executeQuery();
        
        assertTrue(rs.next());
        d = rs.getDate(1);
        c = rs.getInt(2);
        assertEquals(0, d.getTime()); // Date bucketed into 0 hr
        assertEquals(1, c);
        
        assertTrue(rs.next());
        d = rs.getDate(1);
        c = rs.getInt(2);
        assertEquals(1 * 60 * 60 * 1000, d.getTime()); // Date bucketed into 1 hr
        assertEquals(3, c);
        
        assertTrue(rs.next());
        d = rs.getDate(1);
        c = rs.getInt(2);
        assertEquals(2 * 60 * 60 * 1000, d.getTime()); // Date bucketed into 2 hr
        assertEquals(1, c);
        
        assertTrue(rs.next());
        d = rs.getDate(1);
        c = rs.getInt(2);
        assertEquals(4 * 60 * 60 * 1000, d.getTime()); // Date bucketed into 4 hr
        assertEquals(1, c);

        assertFalse(rs.next());
    } finally {
        conn.close();
    }
}
 

@Test
public void testSaltedOrderBy() throws Exception {
    String tablename=generateUniqueName();
    String ddl = "create table " + tablename +
            "   (organization_id char(15) not null," +
            "    \"DATE\" date not null," +
            "    feature char(1) not null," +
            "    unique_users integer not null,\n" +
            "    db_utilization decimal(31,10),\n" +
            "    transactions bigint,\n" +
            "    cpu_utilization decimal(31,10),\n" +
            "    response_time bigint,\n" +
            "    io_time bigint,\n" +
            "    region varchar,\n" +
            "    unset_column decimal(31,10)\n" +
            "    CONSTRAINT pk PRIMARY KEY (organization_id, \"DATE\", feature, unique_users)) salt_buckets=3";
    Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
    try(Connection conn = DriverManager.getConnection(getUrl(), props);) {
        conn.createStatement().execute(ddl);
    }

    String tenantId = getOrganizationId();
    Date startDate = new Date(System.currentTimeMillis());
    initDateTableValues(tablename, tenantId, getSplits(tenantId), startDate);
    // Add more date data
    props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
    try(Connection conn = DriverManager.getConnection(getUrl(), props);) {
        initDateTableValues(tablename, conn, tenantId, new Date(startDate.getTime() + MILLIS_IN_DAY * 10),
                2.0);
        initDateTableValues(tablename, conn, tenantId, new Date(startDate.getTime() + MILLIS_IN_DAY * 20),
                2.0);
        conn.commit();
    }

    props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
    try(Connection conn = DriverManager.getConnection(getUrl(), props);){
        PreparedStatement statement = conn.prepareStatement("SELECT count(1) FROM "+tablename+" WHERE organization_id = ?");
        statement.setString(1, tenantId);
        ResultSet rs = statement.executeQuery();
        assertTrue(rs.next());
        assertEquals(18, rs.getLong(1));

        statement = conn.prepareStatement("SELECT \"DATE\" FROM "+tablename+" WHERE organization_id = ?  order by \"DATE\" desc limit 10");
        statement.setString(1, tenantId);
        rs = statement.executeQuery();
        Date date = null;
        int count = 0;
        while (rs.next()) {
            if (date != null) {
                assertTrue(date.getTime() >= rs.getDate(1).getTime());
            }
            count++;
            date = rs.getDate(1);
        }
        assertEquals(10,count);
    }
}