JPA Time
Time zone offset of DATETIME columns using JPA.
Given:
create table DateTimeTest (
id int not null,
dateTimeValue datetime not null,
unique key id (id)
)
Executing:
dateTime = new GregorianCalendar( UTC_TIME_ZONE );
dateTime.clear();
dateTime.set( 2015, Calendar.MARCH, 29, 13, 0, 0 );
stmt = connection.createStatement();
try {
stmt.executeUpdate(
"insert into DateTimeTest (id,dateTimeValue)"
+ " values (1,'" + formatSqlDateTime( dateTime ) + "')");
}
finally {
stmt.close();
}
Yields the following in the database:
id: 1
dateTimeValue: 2015-03-29 13:00:00
Additionally, executing:
dateTime = new GregorianCalendar( UTC_TIME_ZONE );
stmt = connection.createStatement();
try {
rs = stmt.executeQuery(
"select dateTimeValue"
+ " from DateTimeTest"
+ " where id=1" );
rs.next();
parseSqlDateTime( dateTime, rs.getString( 1 ) );
System.out.println( formatIso8601DateTime( dateTime ) );
}
finally {
stmt.close();
}
Produces the following output:
2015-03-29T13:00:00Z
In contrast, using JPA and given the following class:
@Entity
@Table( name = "DateTimeTest" )
@Access( AccessType.FIELD )
class DateTimeTest {
@Id
@Column( name = "id", nullable = false )
private int id;
@Column( name = "dateTimeValue", nullable = false )
private Calendar dateTimeValue;
/* constructors, setters, getters */
}
Executing:
dateTime = new GregorianCalendar( UTC_TIME_ZONE );
dateTime.clear();
dateTime.set( 2015, Calendar.MARCH, 29, 13, 0, 0 );
dateTimeTest = new DateTimeTest( 1, dateTime );
entityManager.getTransaction().begin();
entityManager.persist( dateTimeTest );
entityManager.getTransaction().commit();
Yields the following in the database:
id: 1
dateTimeValue: 2015-03-29 08:00:00
Although this is different than the value inserted using JDBC, executing:
DateTimeTest dateTimeTest = entityManager.find( DateTimeTest.class, 1 );
System.out.println( formatIso8601DateTime( dateTimeTest.getDateTimeValue() ) );
Produces the following output:
2015-03-29T08:00:00-05
which is equivalent to the original time.
The potential problem is that the time zone offset will be different if, for example, the application is executed on a computer with a different time zone setting.
