Joda time DateTime incorrectly stores in database

Question

I'm storing JodaTime DateTime field to timestamptz column by using org.jadira.usertype:usertype.jodatime:1.9. App server has +4 time zone. DB server +9 time zone. new DateTime() results in ${currentTime+1hour}+9 where +9 is time zone (correct value is ${currentTime+5hours)+9).

I haven't found any related topics. java.util.Date stores correctly.

Domain object has the following mapping property:

static mapping = {
    dateCreated sqlType:'timestamptz'
}

How can I store DateTime correctly?

score 10 · Answer 1 · answered May 26 '13 at 15:33

10

Just set JPA properties:

<property name="jadira.usertype.autoRegisterUserTypes"
          value="true"/>
<property name="jadira.usertype.databaseZone"
          value="jvm"/>
<property name="jadira.usertype.javaZone"
          value="jvm"/>

answered May 26 '13 at 15:33

Michal Zmuda

5,381
3
43
39

1

What file does this go in? – dgrant Jun 06 '15 at 00:51
@dgrant in your persistence.xml file – Hamish Oct 22 '15 at 16:35
Is there a way of doing this without an XML file? My project is using pure annotations for config so far... – Jules Apr 30 '17 at 16:12

score 8 · Answer 2 · answered Mar 13 '13 at 22:48

8

I had the same issue. Specifying app and db zones in config solved the issue.

            <prop key="jadira.usertype.autoRegisterUserTypes">true</prop>
            <prop key="jadira.usertype.databaseZone">America/Los_Angeles</prop>
            <prop key="jadira.usertype.javaZone">America/Los_Angeles</prop>

answered Mar 13 '13 at 22:48

Olja

81
1
3

1

What file does this go in? – dgrant Jun 06 '15 at 00:51

score 6 · Accepted Answer · answered Apr 12 '12 at 07:43

6

Ok. I have spend 8 hours to solve the problem. If your are using usertype project to persist JodaTime, you have to set databaseZone property of PersistentDateTime class equals to current application server timezone (not database!).

But it's better to use official hibernate support. It solves the problem out of the box because it uses java.utl.Date to persist DateTime and java.util.Date correctly persisted by default

answered Apr 12 '12 at 07:43

fedor.belov

22,343
26
89
134

10

official hibernate support does not extend to Hibernate 4.0. :-( for which you need to use usertype. – drone.ah Feb 13 '13 at 18:47

score 1 · Answer 4 · answered Apr 11 '12 at 03:50

1

Try starting JVM with -Duser.timezone=UTC to JAVA_OPTS that way the time is in one zone and you can then do your operations on that to convert it to where ever you are.

answered Apr 11 '12 at 03:50

allthenutsandbolts

1,513
1
13
34

That's a great way to go - but be warned that you can't do that if you already have dates stored in another time zone in the DB. In other words, no problem for new projects but be very, very wary of doing this for ones with existing data. – RedYeti Aug 14 '13 at 11:30

score 0 · Answer 5 · answered Apr 15 '12 at 07:11

0

Fedor,

Am I right to assume you are using Oracle TIMESTAMP WITH TIME ZONE column? Usertype doesn't support this type yet (i.e. with TIME ZONE) due to the handling with JDBC differing between databases, although the project will be happy to accept patches.

There's some good discussion wrt Oracle here: http://puretech.paawak.com/2010/11/02/how-to-handle-oracle-timestamp-with-timezone-from-java/

answered Apr 15 '12 at 07:11

Chris Pheby

323
1
8

we are using PostgreSQL timestamptz – fedor.belov Apr 16 '12 at 14:22

score 0 · Answer 6 · answered Jan 23 '13 at 09:35

I had resolved this problems by creating other PersistentDateTime extends AbstractVersionableUserType.

import java.sql.Timestamp;

import org.hibernate.SessionFactory;
import org.hibernate.usertype.ParameterizedType;
import org.jadira.usertype.dateandtime.joda.columnmapper.TimestampColumnDateTimeMapper;
import org.jadira.usertype.spi.shared.AbstractVersionableUserType;
import org.jadira.usertype.spi.shared.IntegratorConfiguredType;
import org.joda.time.DateTime;

public class PersistentDateTime extends AbstractVersionableUserType<DateTime, Timestamp, TimestampColumnDateTimeMapper> implements ParameterizedType, IntegratorConfiguredType {

@Override
public int compare(Object o1, Object o2) {
    return ((DateTime) o1).compareTo((DateTime) o2);
}

@Override
public void applyConfiguration(SessionFactory sessionFactory) {

    super.applyConfiguration(sessionFactory);

    TimestampColumnDateTimeMapper columnMapper = (TimestampColumnDateTimeMapper) getColumnMapper();
    columnMapper.setDatabaseZone(null);
    columnMapper.setJavaZone(null);
}
}

score 0 · Answer 7 · answered Jan 24 '20 at 17:36

0

Just an update for Spring Boot users. I was able to do the following:

spring.jpa.properties.jadira.usertype:
  autoRegisterUserTypes: true
  databaseZone: jvm
  javaZone: jvm

I put this in my application.yaml file.

answered Jan 24 '20 at 17:36

gaoagong

1,177
14
15

Joda time DateTime incorrectly stores in database

7 Answers7

Linked