Storing joda-time datetime in database

Question

My application already uses Joda-time for date manipulation with plans to upgrade to jsr 310 in the near future.

Now I need to store VERY accurate timestamp in the database using jpa 2 and hibernate, but java.util.Date is not storing the millisecond value.

I found UserType and configured my entitybean like this

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

but it still doesn't store the milliseconds.

I passed in this 2010-11-02 12:02:54.945 but when I retrieved it, it was 2010-11-02 12:02:54.000.

What else do I have to do to get the milliseconds stored and possibly the time zone?

UPDATE I have just checked and the milliseconds actually make it to the database but Hibernate does not include it in the returned results. The same formatter printed the two results so it can't be formatting problem

Answer 1

This might not be exactly what you want, but you could store the time as a number (=miliseconds since epoch) instead of a datetime field.

Answer 2

Oracle DATE datatype does not store fractional seconds. You need to use TIMESTAMP or a numeric column as previously suggested. See: http://knol.google.com/k/oracle-date-and-time-data-types