How to store joda DateTime in SQL Server database with hibernate

Question

I have weird problem when storing DateTime in database using hibernate and joda time.

I have a bean like this :

import org.joda.time.DateTime;

public class RelationshipEntity  {

    @Column(name = "date_revision")
    @Type(type = "org.jadira.usertype.dateandtime.joda.PersistentDateTime")
    private DateTime dateRevision;

    // other fields
}

I have set the timezone to Europe/Paris (GMT+1) in the application with TimeZone.setDefault(TimeZone.getTimeZone("Europe/Paris"));

If I save the entity with a revision date to 2017-01-19 13:24 into the database, I can see the date in the database has been set to 2017-01-19 12:24 and I don't understand why.

The database timezone also seems to be Europe/Paris because :

SELECT SYSDATETIME(); //2017-01-19 14:41

SELECT SYSUTCDATETIME(); // 2017-01-19 13:41

As I use spring-boot I tried to add theses properties in application.yml

jadira.usertype.autoRegisterUserTypes: true
jadira.usertype.databaseZone: jvm
jadira.usertype.javaZone: jvm

and start the application with the argument -Duser.timezone=Europe/Paris but it still saves the date in UTC into the database.

To solve the problem I changed DateTime to LocalDateTime :

@Column(name = "date_revision")
@Type(type = "org.jadira.usertype.dateandtime.joda.PersistentLocalDateTime")
private DateLocalTime dateRevision;

With this change, the dateRevision in the database is the same I set in my java bean.

Can someone explain me why it converts the date to UTC when I use the type org.joda.time.DateTime ?

NOTE : I use Hibernate 4 and joda-time 2.5

Doesnt it make sense that there is universal standard when saving time to db? What if 2 users in two different time zones use it? — notanormie, Jan 19 '17 at 13:55
If the timezone of my app and the database are the same, I don't understand why I get a difference between the date in java and the date in db. Is the default behavior to convert the date in UTC when using DateTime ? — Olivier Boissé, Jan 19 '17 at 14:03

score 4 · Accepted Answer · edited May 23 '17 at 12:08

4

This is because zoda DateTime store the date in db in UTC and LocalDateTime stores the date as it is and should be used when you do not care about the time zone.

In case of DateTime when you need to consider time zones (day light savings etc) it handles it that way and stores the date in Universal time.

Please refer this answer for detailed info.

edited May 23 '17 at 12:08

Community

1
1

answered Jan 19 '17 at 14:11

Rohit Gulati

542
3
15

I think you're right, the documentation [PersistentDateTime](http://jadira.sourceforge.net/apidocs/org/jadira/usertype/dateandtime/joda/PersistentDateTime.html) says _The type is stored using UTC timezone and presented in the JVM using the JVM's default zone_, that's why I get this behavior – Olivier Boissé Jan 19 '17 at 14:32

How to store joda DateTime in SQL Server database with hibernate

1 Answers1