CODEWITHSUNDEEP
sql-server-2008hibernate-mappingjava.util.date
Waruzjan
Waruzjan

Reputation: 79

Hibernate Date mapping to SQL Server datetime rounds by 1ms

I have searched a lot on stackoverflow and found out that mapping java.util.Date to SQL databases often causes problems and possible solutions are to

However, in all the cases I have seen, the problem was that when the date was saved, the last field (e.g. millieseconds) was lost (e.g. 000). In my case, it's a little different, consider following code:

public class TestDateTime implements Serializable {
    private long id;
    private Date created;
    private Calendar published;

    public TestDateTime(Date created, Date published) {
        this.created = created;
        this.published = GregorianCalendar.getInstance();
        this.published.setTime(published);
    }
}

<class name="TestDateTime" table="TestDateTime">
    <id name="id" type="long" column="id">
        <generator class="identity" />
    </id>
    <property name="created" column="created"/>
    <property name="published" column="published"/>
</class>

private void testSaveDate(HibernateTemplate hibernateTemplate) {
    long time = 1357747095488l;

    TestDateTime tdt = new TestDateTime(new Date(time), new Date(time));

    System.out.println("Before saving created:" + tdt.getCreated().getTime() + " (" + tdt.getCreated() + ")"
        + ", published:" + tdt.getPublished().getTime().getTime() + " (" + tdt.getPublished().getTime() + ")");
    Serializable id = hibernateTemplate.save(tdt);

    TestDateTime saved = hibernateTemplate.get(TestDateTime.class, id);

    System.out.println("After saving created:" + saved.getCreated().getTime() + " (" + saved.getCreated() + ")"
        + ", published:" + saved.getPublished().getTime() + " (" + saved.getPublished() + ")");
}

Results in the following output on the console:

[INFO] Before saving created:1357747095488 (Wed Jan 09 16:58:15 CET 2013), published:1357747095488 (Wed Jan 09 16:58:15 CET 2013)
[INFO] Hibernate: insert into TestDateTime (created, published) values (?, ?)
[INFO] Hibernate: select testdateti0_.id as id42_0_, testdateti0_.created as created42_0_, testdateti0_.published as published42_0_ from TestDateTime testdateti0_ where testdateti0_.id=?
[INFO] After saving created:1357747095487 (2013-01-09 16:58:15.487), published:Wed Jan 09 16:58:15 CET 2013 (java.util.GregorianCalendar[time=1357747095487,areFieldsSet=true,areAllFieldsSet=true,lenient=true,zone=sun.util.calendar.ZoneInfo[id="Europe/Berlin",offset=3600000,dstSavings=3600000,useDaylight=true,transitions=143,lastRule=java.util.SimpleTimeZone[id=Europe/Berlin,offset=3600000,dstSavings=3600000,useDaylight=true,startYear=0,startMode=2,startMonth=2,startDay=-1,startDayOfWeek=1,startTime=3600000,startTimeMode=2,endMode=2,endMonth=9,endDay=-1,endDayOfWeek=1,endTime=3600000,endTimeMode=2]],firstDayOfWeek=2,minimalDaysInFirstWeek=4,ERA=1,YEAR=2013,MONTH=0,WEEK_OF_YEAR=2,WEEK_OF_MONTH=2,DAY_OF_MONTH=9,DAY_OF_YEAR=9,DAY_OF_WEEK=4,DAY_OF_WEEK_IN_MONTH=2,AM_PM=1,HOUR=4,HOUR_OF_DAY=16,MINUTE=58,SECOND=15,MILLISECOND=487,ZONE_OFFSET=3600000,DST_OFFSET=0])

So after saving the date into SQL server, it changes 1ms from 1357747095488 into 1357747095487. Is this the expected behaviour of the datetime type of SQL Server?

Furthermore, I have to mention that the Date objects are created by JackRabbit during checkin, thus I can't truncate this date in JackRabbit, to get it synchronized with its value stored in SQL Server.

The problem is that in my application I have to compare this dates, some coming from JackRabbit and some from database (which where originally also created by jackrabbit). Since the version of the date in sql server is different then the one from JackRabbit, I can't compare them correctly.

What would be the best solution for me, that is multi database complient (we use both MySql and MsSql 2008 R2). Is there a standard way to map the java.util.Date to a bigint column, such that I can store the long value myself? Or should I create my UserType? And is this a bad idea, since I still can compare the longs in queries (and I assume Hibernate also with the proper mappings)

Upvotes: 1

Views: 4037

Answers (1)

HABO
HABO

Reputation: 15816

The accuracy of the DateTime data type in SQL Server is about 3ms. See here. The DateTime2 data type is more accurate. See here.

Upvotes: 1

Related Questions