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
Am Donnerstag, 26. Juli 2012 12:21:12 UTC+2 schrieb 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
>
> Am Dienstag, 24. Juli 2012 19:58:42 UTC+2 schrieb 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
>>
>>
>> On Fri, Jul 20, 2012 at 4:19 PM, Maarten Donders
>> <[email protected]> wrote:
>> > 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");
>> > }
>> >
>> > --
>> > You received this message because you are subscribed to the Google
>> Groups "H2 Database" group.
>> > 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.
>> >
>>
>
--
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/-/Kpzbnt9C5V4J.
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.
