To prove that is really a h2 problem, I ran my test program against MySQL
(version 5.5.24 on Mac OS X): In that case, it reacts as the old h2
version. To try it out, change the first 2 lines of the test program to:
Class.forName( "com.mysql.jdbc.Driver" );
Connection conn = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/tmsng?useUnicode=true&characterEncoding=utf8&socketTimeout=120000&useLegacyDatetimeCode=false&serverTimezone=UTC",
"root", "" );
I enabled the MySQL query log and it shows that these queries 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, '2010-10-31 01:00:00.0',
'2010-10-31 00:55:00.0', 0);
SELECT * FROM TestTable where (('2010-10-31 00:55:00.0' < end_time and
'2010-10-31 01:00:00.0' >= end_time) or ('2010-10-31 01:05:00.0' > end_time
and '2010-10-31 01:00:00.0' <= end_time and '2010-10-31 01:00:00.0' >
start_time))
I hope this helps to find the problem.
regards,
Wim
Op maandag 15 oktober 2012 13:58:56 UTC+2 schreef Wim Deblauwe het volgende:
>
> 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/-/Mz8z10tUBGIJ.
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.
