Calendar impreciseness when persisting to MySql

AbsBas · **Joined:** Mon Feb 05, 2007 2:51 am **Posts:** 2

Hello,

I have a problem with persisting a Calendar property to MySql.

I'm using hibernate version 3.2.0 GA and MySql version 5.0.27.

I want to persist the following property to MySql DB:

Code:

    private Calendar datumVon;

    @RepositoryInformation(description = "gültig von")
    @XmlElement(name = "DatumVon", type = String.class)
    @XmlJavaTypeAdapter(DateXmlAdapter.class)

    @Temporal(TemporalType.DATE)

    @Column(name = "DATUMVON", unique = false, nullable = true, insertable = true, updatable = true, length = 7)

    public Calendar getDatumVon() {
        return this.datumVon;
    }

    public void setDatumVon(Calendar datumVon) {
        this.datumVon = datumVon;
    }

Usually this will work fine.
But when I try to insert a date like 0001.01.01 (yyyy.mm.dd)
the value 0001.01.03 is written into database. Further tests showed
some kind of change when inserting different dates:

Code:

                    in                  out
Original:        0001-01-01 | 0001-01-03
> J0500:        0500-01-01 | 0499-12-31
> J1000:        1000-01-01 | 0999-12-27
> J1500:        1500-01-01 | 1499-12-23
> J1550:        1550-01-01 | 1549-12-22
> J1580:        1580-01-01 | 1579-12-22
> J1581:        1581-01-01 | 1580-12-22
> J1582:        1582-01-01 | 1581-12-22
> J1583:        1583-01-01 | 1583-01-01 <- starting correct values
> J1584:        1584-01-01 | 1584-01-01
> J1585:        1585-01-01 | 1585-01-01
> J1590:        1590-01-01 | 1590-01-01
> J1599:        1599-01-01 | 1599-01-01
> J1600:        1600-01-01 | 1600-01-01
> J1700:        1700-01-01 | 1700-01-01
> J1800:        1800-01-01 | 1800-01-01
> J1900:        1900-01-01 | 1900-01-01
> J2000:        2000-01-01 | 2000-01-01
> J5000:        5000-01-01 | 5000-01-01
> Maximum:        9999-12-31 | 9999-12-31

As the Calendar-object seems to keep the correct value,
we assumed MySql would store it incorrectly.

Well, doing the same on an oracle db using OracleDialect
solved the problem (the values were correct) but it showed that MySql, directly inserted to, could store that values correctly as well.

The hibernate trace showed the inserts were allready wrong:

Code:

   DEBUG   1170660984343   2007/02/05-08:36:24   binding '2007-02-05 08:36:24' to parameter: 1
   DEBUG   1170660984359   2007/02/05-08:36:24   binding '2007-02-05 08:36:24' to parameter: 2
-> DEBUG   1170660984359   2007/02/05-08:36:24   binding '0001-01-03 00:00:00' to parameter: 3
   DEBUG   1170660984359   2007/02/05-08:36:24   binding 'DE' to parameter: 4
   DEBUG   1170660984359   2007/02/05-08:36:24   binding '04251' to parameter: 5

So the error happens somewhere around the persistence-process??
Around org.hibernate.type.CalendarType?

I have no explanation so far nor a workaround since the original
date even if absurd must be processed.

Anybody have an idea?
Thanks in advance..

emmanuel · **Posted:** Mon Feb 05, 2007 12:04 pm

Here is what Hibernate does

Code:

public Object get(ResultSet rs, String name) throws HibernateException, SQLException {

      Timestamp ts = rs.getTimestamp(name);
      if (ts!=null) {
         Calendar cal = new GregorianCalendar();
         if ( Environment.jvmHasTimestampBug() ) {
            cal.setTime( new Date( ts.getTime() + ts.getNanos() / 1000000 ) );
         }
         else {
            cal.setTime(ts);
         }
         return cal;
      }
      else {
         return null;
      }

   }

   public void set(PreparedStatement st, Object value, int index) throws HibernateException, SQLException {
      final Calendar cal = (Calendar) value;
      //st.setTimestamp( index,  new Timestamp( cal.getTimeInMillis() ), cal ); //JDK 1.5 only
      st.setTimestamp( index,  new Timestamp( cal.getTime().getTime() ), cal );
   }

AbsBas · **Joined:** Mon Feb 05, 2007 2:51 am **Posts:** 2

Ok, thanks for your help.

My Calendar-object returned an invalid Millis-value.

Bye!