I want to store a LocalDate
in a DATE
column and retrieve it unchanged. Both DATE
and LocalDate
are "local" types by definition. Therefore, the concept of timezone should not interfere in any way.
The code below is a minimal example that creates a table with a DATE
column in a in-memory database. The maven artifact com.h2database:h2:1.4.192
must be in the classpath.
First, define methods insert
and retrieve
:
static void insert(DataSource ds, String date) throws SQLException {
try (Connection conn = ds.getConnection();
Statement stmt = conn.createStatement()) {
stmt.execute("CREATE TABLE people (id BIGINT NOT NULL AUTO_INCREMENT"
+ ", born DATE NOT NULL, PRIMARY KEY (id) );");
stmt.execute("INSERT INTO people (born) VALUES ('" + date + "')");
}
}
static LocalDate retrieve(DataSource ds) throws SQLException {
try (Connection conn = ds.getConnection();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM people limit 1")) {
if (rs.next()) {
java.sql.Date retrieved = java.sql.Date.valueOf(rs.getString("born"));
return retrieved.toLocalDate();
}
throw new IllegalStateException("No data");
}
}
Notice that the insert
method uses the toString
value of the LocalDate
in single quotes, so there's no opportunity for Java™ to create timezone ambiguity. Now call insert
once and then several times retrieve
, with different timzone settings each time:
public static void main(String[] args) throws Exception {
DataSource ds = JdbcConnectionPool.create("jdbc:h2:mem:test", "sa", "sa");
LocalDate born = LocalDate.parse("2015-05-20");
insert(ds, born.toString());
System.out.println("Inserted: " + born);
for (int i : new int[]{-14, 0, 12}) {
TimeZone z = TimeZone.getTimeZone(String.format("Etc/GMT%+02d", i));
TimeZone.setDefault(z);
System.out.println("Retrieved: " + retrieve(ds));
}
}
Then the following is printed:
Inserted: 2015-05-20 Retrieved: 2015-05-20 Retrieved: 2015-05-19 Retrieved: 2015-05-18
How to write the retrieve
method so that it returns the same value that was inserted unconditionally, assuming that the database table doesn't change?
I just tried the following modification to your retrieve
method and it worked for me:
The H2 documentation for the DATE Type says that it is
The date data type. The format is yyyy-MM-dd.
So, instead of your ...
java.sql.Date retrieved = (java.sql.Date) rs.getObject("born");
return retrieved.toLocalDate();
... I just used ...
return LocalDate.parse(rs.getString("born"));
... and my code produced
Inserted: 2015-05-20
Retrieved: 2015-05-20
Retrieved: 2015-05-20
Retrieved: 2015-05-20