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
The following solution also works. I prefer the conversion via String
in the accepted answer, because it avoids the timezone tinkering shown below. It may however not work the same way on all databases because some, e.g. Oracle, have a different definition of 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()) {
ZoneId utc = ZoneId.of("UTC");
TimeZone z = TimeZone.getTimeZone(utc);
Calendar cal = Calendar.getInstance(z);
java.sql.Date retrieved = rs.getDate("born", cal);
long time = retrieved.getTime();
java.util.Date utilDate = new java.util.Date(time);
Instant instant = utilDate.toInstant();
ZonedDateTime zdt = instant.atZone(utc);
return zdt.toLocalDate();
}
}
throw new IllegalStateException("No data");
}
The conversion via java.util.Date
is outlined in this question, as suggested by user Tunaki: Missed opportunity to fix JDBC date handling in Java 8?
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With