org.h2.jdbc.JdbcPreparedStatement.setTimestamp() not handling utc dates correctly?

[Jamie Clark]

I am attempting to insert unique UTC dates crossing a DST boundary,
and receive a unique constraint violation.

Upon investigation, it appears H2 is incorrectly adjusting the
timestamp when calling JdbcPreparedStatement.setTimestamp(int
parameterIndex, java.sql.Timestamp x, Calendar calendar) (line 654).

Environment details:
H2 1.3.158
java version "1.6.0_24"
I am running H2 in-memory, oracle mode
I have a table which has a timestamp column (UTC) with a unique
constraint.
My system timezone is PST/PDT

My scenario is the following:

I attempt to insert 15 minute intervals starting 2010-03-13 00:00:00.0
through 2010-03-16 00:00:00.0 (crossing DST Boundary) via iBatis:

Timestamp timestamp = new Timestamp(date.getTime());
setter.setTimestamp(timestamp, getCalendarUTC());

I receive the following exception:
--- Cause: org.h2.jdbc.JdbcSQLException: Unique index or primary
key violation: "PRIMARY_KEY_3 ON PUBLIC.LP_INTERVALS(CHANNEL_ID,
UTC_INTERVAL_TIME)"

I have narrowed the problem down to
DateTimeUtils.convertTimestampToUTC(). It seems that underlying long
value of timestamp is being modified by the calendar, which is
incorrect as it is already in GMT.

I have written the following to expose the problem:
@Test
public void test ()
{
Date now = new Date();
System.out.println (now.getTime());

Timestamp x = new Timestamp(now.getTime());

Calendar c =
Calendar.getInstance(TimeZone.getTimeZone("UTC"));

ValueTimestamp v = (ValueTimestamp)
DateTimeUtils.convertTimestampToUTC (x, c);
Timestamp y = v.getTimestamp();
System.out.println (y.getTime());

assert(x.getTime() == y.getTime()); // fails
}

The existence of the methodconvertTimestampToUTC confuses me a little,
since a timestamp value is already in GMT, what conversion needs to be
done?

[Thomas Mueller]

Hi,

> Upon investigation, it appears H2 is incorrectly adjusting the
> timestamp when calling JdbcPreparedStatement.setTimestamp(int
> parameterIndex, java.sql.Timestamp x, Calendar calendar) (line 654).

Obviously you shouldn't use ValueTimestamp directly. Instead, use the JDBC API.

Did you check what other databases do? According to my test cases, H2
works in the same way as other databases (HSQLDB, Derby,
PostgreSQL,...).

Regards,
Thomas

[Jamie Clark]

Sorry for the delay in getting back to - was pulled in another direction for a while.

Of course I am not using ValueTimestamp directly, was just trying to cut to the chase, but clearly did not give you enough context to work with.

Here is the problem I am currently encountering, and a very simple way to reproduce:

1. Create a table with a timestamp column which has a unique constraint.

2. Running in PST/PDT (or probably anywhere west of GMT), attempt to insert the following dates using utc calendar (ps.setTimestamp(new Timestamp(d.getTime(), utcCalendar):

    // assume dates properly created in PST

    "2010-03-13 18:15 PST"

    "2010-03-13 19:15 PST"

 

    The first date inserts correctly, the second date throws a unique constraint exception.

You may notice these dates are not actually crossing a DST boundary yet.  This is what initially confused me when digging through the source code.  Because the timestamps are being modified under the covers, the problem happens at an unexpected time (my first unit tests passed when they should have failed because I covered exactly the DST boundary instead of 8 hours before... very confusing!)

The exception appears to be a result of ValueTimestamp.compareSecure, however I do not have the source code correctly linked  so I am unable to introspect the actual values.  I did notice that at some point during the compare process DateTimeUtils.dateValueFromDate(long ms) is being called, which is ultimately returning:

 return ((long) year << SHIFT_YEAR) | (month << SHIFT_MONTH) | day; 

This doesn't look right to me (where are the hours/minutes/seconds/millis?), but honestly at this point my head is swimming from all the layers.

Any help on this would be greatly appreciated.  I have a massive test framework built on top of this which will only be useful if I can solve this issue.

Thanks in advance for your time!

Jamie

[Noel Grandin]

I don't know what is wrong, but this is a test-case that shows the problem, to be used in the TestDateStorage class

private void testXXX() throws SQLException {
Connection conn = getConnection("date");
TimeZone defaultTimeZone = TimeZone.getDefault();
Statement stat = conn.createStatement();
stat.execute("create table test(ts timestamp primary key)");
try {
TimeZone.setDefault(TimeZone.getTimeZone("PST"));
DateTimeUtils.resetCalendar();
java.sql.Timestamp date1 = java.sql.Timestamp.valueOf("2010-03-13 18:15:00");
java.sql.Timestamp date2 = java.sql.Timestamp.valueOf("2010-03-13 19:15:00");
Calendar utcCalendar = new GregorianCalendar(new SimpleTimeZone(0, "Z"));
PreparedStatement prep = conn.prepareStatement("INSERT INTO TEST VALUES(?)");
prep.setTimestamp(1, new java.sql.Timestamp(date1.getTime()), utcCalendar);
prep.execute();
prep.setTimestamp(1, new java.sql.Timestamp(date2.getTime()), utcCalendar);
prep.execute();
} finally {
TimeZone.setDefault(defaultTimeZone);
DateTimeUtils.resetCalendar();
}
conn.close();
deleteDb("date");
}

Jamie Clark wrote:
> 2010-03-13 18:15

[Thomas Mueller]

Hi,

Thanks a lot for the test case! I can reproduce the problem now, and
I'm working on a solution.

Currently, H2 converts the timestamp to the local timezone before
getting the fields (year, month, day, hour,...). This is doesn't work
in this case, as the converted time doesn't actually exist (due to
summer time change; it's 02:15 am).

I think I can fix this in the next release. Of course I will add your
test case (plus a few more).

Regards,
Thomas

> --
> You received this message because you are subscribed to the Google Groups
> "H2 Database" group.
> To post to this group, send email to h2-da...@googlegroups.com.
> To unsubscribe from this group, send email to
> h2-database...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/h2-database?hl=en.
>
>

[Thomas Mueller]

Hi,

This problem is now fixed in the trunk.

Regards,
Thomas