How to store Joda DateTime in MySQL

Question

I've recently started using Joda time library for my test project. Particularly i have been enjoying the capabilities of DateTime and functions for its manipulation.

My query is how do you store DateTime in MySql. I am using Spring & Hibernate for my application.

my current entity throws deserialisation errors whenever I try and use it:

@Entity
@Table(name = "test_storage")
public class TestEntity {
    @Id @GeneratedValue
    private int id;

    @Column
    private DateTime testDate;

    //getters and setters
}

The mysql table structure is as follows:

Name: test_storage
Columns:
id         INT NOT_NULL, AUTO_INCREMENT
testDate   DATETIME

Any advice?

Answer 1

If you are using Hibernate 4+, then you can adopt the Jadira user types which allow you to map DateTime (and other JODA date time related class like LocalDate, LocalDateTime etc) to DB fields using different strategies.

Your mapping will look like

public class TestEntity {
    //...

    @Column
    @Type(type="org.jadira.usertype.dateandtime.joda.PersistentDateTime")
    private DateTime testDate;
}

Read the documents to know how to properly use these types to fit your requirements.

The biggest pitfall that you may face soon is, as Java's Date does not include timezone information nor does it sticks to UTC (JODA's user types still need to map to Timestamp/Date internally), you may want to make sure the way you store does provide proper information. For example, either store the date time as UTC, or store timezone information as a separate field, etc.

Answer 2

DATETIME would be my choice. See some more details at What difference between the DATE, TIME, DATETIME, and TIMESTAMP Types and http://infopotato.com/blog/index/datetime_vs_timestamp