These are the SQL statements that are executed:
create table TestTable (
detector_id integer not null,
data_number bigint not null,
end_time datetime not null,
start_time datetime not null,
is_combined bit,
primary key (detector_id, end_time, data_number)
);
INSERT INTO TestTable VALUES( ?, ?, ?, ?, ?) {1: 1, 2: 1, 3:
TIMESTAMP '2010-10-31 02:00:00.0', 4: TIMESTAMP '2010-10-31 02:55:00.0', 5:
FALSE};
SELECT * FROM TestTable;
SELECT * FROM TestTable where ((? < end_time and ? >= end_time) or (? >
end_time and ? <= end_time and ? > start_time)) {1: TIMESTAMP '2010-10-31
02:55:00.0', 2: TIMESTAMP '2010-10-31 02:00:00.0', 3: TIMESTAMP '2010-10-31
02:05:00.0', 4: TIMESTAMP '2010-10-31 02:00:00.0', 5: TIMESTAMP '2010-10-31
02:00:00.0'};
regards,
Wim
Op woensdag 10 oktober 2012 10:31:15 UTC+2 schreef Steve McLeod het
volgende:
>
> 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/-/YNghbD0k9x0J.
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.
