wrong unique contraint violation for different timestamps

[Maarten Donders]

On an attempt to insert unique dates crossing a DST boundary I receive
a wrong unique constraint violation.

Here is a test case to insert into the TestDataStorage class:

private void testDST() throws SQLException {
Connection conn = getConnection("date");
Statement stat = conn.createStatement();
TimeZone defaultTimeZone = TimeZone.getDefault();
stat.execute("create table test(ts timestamp primary key)");
try {
TimeZone.setDefault(TimeZone.getTimeZone("Europe/Berlin"));
Calendar calendar = new GregorianCalendar(TimeZone.getDefault());

PreparedStatement prep = conn
.prepareStatement("INSERT INTO TEST VALUES(?)");
prep.setTimestamp(1, new java.sql.Timestamp(1162080120000l),
calendar);
prep.execute();
prep.setTimestamp(1, new java.sql.Timestamp(1162083720000l),
calendar);
prep.execute();
} finally {
TimeZone.setDefault(defaultTimeZone);
DateTimeUtils.resetCalendar();
}
conn.close();
deleteDb("date");
}

[Thomas Mueller]

Hi,

java.util.Date automatically converts such times (forward or backward
one hour across DST boundaries). I know this is a problem because
normally, you don't want this "feature". For timezone that have the
DST boundaries at midnight it's really big problem because it can
affect the day (when using java.sql.Date). The problem is that the
times are 'equal' even before they are going into the database:

TimeZone.setDefault(TimeZone.getTimeZone("Europe/Berlin"));
Timestamp a = new Timestamp(1162080120000l);
Timestamp b = new Timestamp(1162083720000l);
System.out.println(a);
System.out.println(b);

Output:

2006-10-29 02:02:00.0
2006-10-29 02:02:00.0

If you insert the values as strings, then no conversion occurs,
because H2 doesn't use java.util.Date and Calendar internally (for
exactly that reason: to avoid problems caused by java.util.Date).

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.
>

[Maarten Donders]

Hi Thomas,

thank you for your much appreciated answer. I see you point regarding the use of java.util.Date.

You write that the times are equal before they are going into the database. However this is not the case:

            Timestamp t1 = new java.sql.Timestamp(1162080120000l);
            Timestamp t2 = new java.sql.Timestamp(1162083720000l);
            System.out.println("Are timestamps equal?: " + t1.equals(t2));
            System.out.println(t1.getTime());
            System.out.println(t2.getTime());

Output:

            Are timestamps equal?: false
            1162080120000
            1162083720000

So it still is strange, that this results in a unique constraint violation, even if the toString()-method returns the same formatted date.

I assumed, that the timestamp is persisted as a long, in this case there should be no unique constraint.
Is it converted to a string and persisted as this string? Or is it converted to a string and back to long?

On the client side we check the dates for equality and as they are not equal a unique constraint violation is unexpected.
As a result from your answer we could implement the equality different (using the formatted string instead of the timestamp), which probably will be the way to go, if there is no way to solve this in H2.

But maybe you still see a solution to handle this different in H2?

Regards,
Maarten

> To unsubscribe from this group, send email to h2-database+unsubscribe@googlegroups.com.

[Maarten Donders]

Hi Thomas,

As an addition to my last post:

We have some automatic tests running which worked with h2 v1.1.117 and started to throw a unique constraint error with v1.3.168 (we migrated from 1.1.117 to 1.3.168 without intermediate versions). So there seems to be a difference in the implementation and behavior compared with the earlier version.

Regards,
Maarten