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