Surely you can reduce your example code to two or three SQL queries that demonstrate the problem? That would make it much easier for us to help.
On Tuesday, 9 October 2012 11:46:53 UTC+2, Wim Deblauwe wrote: > > Hi, > > I recently updated from 1.2.141 to 1.3.168 because I needed support for > .... > > However, when I did this, one of my unit tests started failing. I managed > to extract this into a small test program (see bottom of this email). When > using version 1.2.141, the 2nd query returns the single row that is present > in the database. However, just switching to version 1.3.168 makes it fail > and returns no rows. The > changelog<http://www.h2database.com/html/changelog.html>only contains > information up to version 1.3.159, so it was impossible for > me to check all the changes notes to see if something might have changed in > date handling. I hope somebody who knows the code well can help me out to > see if it is a real bug and hopefully a bugfix then. > > regards, > > Wim > > === > Below is the full code of the test program. You will need to use JodaTime > as well to compile and run it. > === > > package > com.traficon.tmsng.server.common.service.persistence.impl.hibernate; > > import org.joda.time.DateTime; > import org.joda.time.DateTimeZone; > import org.joda.time.Minutes; > > import java.sql.Connection; > import java.sql.DriverManager; > import java.sql.PreparedStatement; > import java.sql.ResultSet; > import java.sql.SQLException; > import java.sql.Statement; > import java.sql.Timestamp; > > public class H2TimeZonesTest > { > public static void main( String[] args ) throws SQLException, > ClassNotFoundException > { > Class.forName( "org.h2.Driver" ); > Connection conn = DriverManager.getConnection( > "jdbc:h2:mem:testdb;MODE=MYSQL;TRACE_LEVEL_SYSTEM_OUT=0;DB_CLOSE_DELAY=-1;AUTOCOMMIT=FALSE", > "sa", "" ); > try > { > createTable( conn ); > > DateTime endTime = new DateTime( 2010, 10, 31, 2, 0, 0, 0, > DateTimeZone.forID( "+01:00" ) ); > DateTime startTime = endTime.minusMinutes( 5 ).withZone( > DateTimeZone.forID( "+02:00" ) ); > > insertTestdata( conn, startTime, endTime ); > printData( conn, "SELECT * FROM TestTable" ); > > System.out.println( "---"); > > printQueryData( conn, startTime, endTime ); > } > finally > { > conn.close(); > } > } > > private static void printQueryData( Connection conn, DateTime startTime, > DateTime endTime ) throws SQLException > { > String sql = "SELECT * FROM TestTable where ((? < end_time and ? >= > end_time) or (? > end_time and ? <= end_time and ? > start_time))"; > PreparedStatement statement = conn.prepareStatement( sql ); > > Timestamp startTimestamp = new Timestamp( startTime.withMillisOfSecond( 0 > ).toDate().getTime() ); > Timestamp endTimestamp = new Timestamp( endTime.withMillisOfSecond( 0 > ).toDate().getTime() ); > Timestamp endTimestampPlusInterval = new Timestamp( endTime.plus( > Minutes.minutes( 5 ) ).withMillisOfSecond( 0 ).toDate().getTime() ); > > statement.setTimestamp( 1, startTimestamp ); > statement.setTimestamp( 2, endTimestamp ); > statement.setTimestamp( 3, endTimestampPlusInterval ); > statement.setTimestamp( 4, endTimestamp ); > statement.setTimestamp( 5, endTimestamp ); > > ResultSet resultSet = statement.executeQuery(); > printResultSet( resultSet ); > } > > private static void printData( Connection conn, String sql ) throws > SQLException > { > Statement statement = null; > try > { > statement = conn.createStatement(); > ResultSet resultSet = statement.executeQuery( sql ); > printResultSet( resultSet ); > } > finally > { > if (statement != null) > { > statement.close(); > } > } > > } > > private static void printResultSet( ResultSet resultSet ) throws > SQLException > { > int rows = 0; > while (resultSet.next()) > { > System.out.println( "detectorId: " + resultSet.getInt( "detector_id" ) ); > System.out.println( "data_number: " + resultSet.getInt( "data_number" ) ); > System.out.println( "end_time: " + resultSet.getTimestamp( "end_time" ) ); > System.out.println( "start_time: " + resultSet.getTimestamp( "start_time" > ) ); > System.out.println( "is_combined: " + resultSet.getBoolean( "is_combined" > ) ); > rows++; > } > System.out.println( "Printed " + rows + " rows" ); > } > > private static void insertTestdata( Connection conn, DateTime startTime, > DateTime endTime ) throws SQLException > { > Statement statement = null; > try > { > > PreparedStatement preparedStatement = conn.prepareStatement( "INSERT INTO > TestTable VALUES(" + > " ?," + > " ?," + > " ?," + > " ?," + > " ?)" ); > preparedStatement.setInt( 1, 1 ); > preparedStatement.setInt( 2, 1 ); > preparedStatement.setTimestamp( 3, new Timestamp( > endTime.toDate().getTime() ) ); > preparedStatement.setTimestamp( 4, new Timestamp( > startTime.toDate().getTime() ) ); > preparedStatement.setBoolean( 5, false ); > > preparedStatement.executeUpdate(); > } > finally > { > if (statement != null) > { > statement.close(); > } > } > > } > > private static void createTable( Connection conn ) throws SQLException > { > Statement statement = null; > try > { > statement = conn.createStatement(); > statement.executeUpdate( "create table TestTable (\n" + > " detector_id integer not null,\n" + > " data_number bigint not null,\n" + > " end_time datetime not null,\n" + > " start_time datetime not null,\n" + > " is_combined bit,\n" + > " primary key (detector_id, end_time, data_number)\n" + > ")"); > } > finally > { > if (statement != null) > { > statement.close(); > } > } > } > } > > -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To view this discussion on the web visit https://groups.google.com/d/msg/h2-database/-/QHkoR2vui44J. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
