How to get a java.time object from a java.sql.Timestamp without a JDBC 4.2 driver?

Question

When retrieving a java.sql.Timestamp from a database via JDBC 4.1 or earlier, how does one obtain/convert to a java.time object?

Neither of the open-source JDBC drivers for Postgres is JDBC 4.2 compliant yet, so I'm looking for a way to use use java.time with JDBC 4.1.

May I ask why you need to use java.time, that appear to be new 1.8 classes? — JBuenoJr, Mar 18 '14 at 04:21
Because java.util.Date & .Calendar are notoriously troublesome. I usually use [Joda-Time](http://www.joda.org/joda-time/‎) instead. But I've been dabbling with java.time in Java 8. The java.time package is inspired by Joda-Time but is re-architected. — Basil Bourque, Mar 18 '14 at 04:43
**UPDATE:** While I accepted [the Answer by pickypg](https://stackoverflow.com/a/22470650/642706) as correctly addressing the specifics of my Question, the best solution is to use only the *java.time* classes with JDBC 4.2 while avoiding entirely the troublesome legacy *java.sql* classes. Exchange *java.time* objects directly with the database; no need for conversion anymore. See the [Answer by skrueger](https://stackoverflow.com/a/50668272/642706). — Basil Bourque, Jun 03 '18 at 21:05

score 51 · Accepted Answer · edited Jun 03 '16 at 10:00

51

New Methods On Old Classes

By using the driver with Java 8 and later, you should automatically pick up some methods on your java.sql.Timestamp object for free. Both java.sql.Time and java.sql.Date have similar conversion methods.

Namely, to convert from java.sql to java.time you are looking for:

To go the other direction, from java.time to java.sql, use the new static methods:

Example:

preparedStatement.setTimestamp( 2, Timestamp.from(instant) );

edited Jun 03 '16 at 10:00

Jens Piegsa

7,399
5
58
106

answered Mar 18 '14 at 04:50

pickypg

22,034
5
72
84

2

Thank you for the answer and the insight. I was focused on the JDBC driver, and did not think to look at the `java.sql.Timestamp` class’ new features. – Basil Bourque Mar 19 '14 at 22:32
3

FYI, after getting an `Instant` as seen in this Answer, you can apply a [`ZoneOffset`](http://docs.oracle.com/javase/8/docs/api/java/time/ZoneOffset.html) to get an [`OffsetDateTime`](http://docs.oracle.com/javase/8/docs/api/java/time/OffsetDateTime.html), or better, apply a [`ZoneId`](http://docs.oracle.com/javase/8/docs/api/java/time/ZoneId.html) to get a [`ZonedDateTime`](http://docs.oracle.com/javase/8/docs/api/java/time/ZonedDateTime.html). For more info, see [my Answer](http://stackoverflow.com/a/36639155/642706) and [my diagram](http://i.stack.imgur.com/5yzFN.png) on another Question. – Basil Bourque Apr 22 '16 at 02:35
1

Upcoming release should have support for java 8 time API: https://jdbc.postgresql.org/documentation/head/8-date-time.html – oᴉɹǝɥɔ Dec 16 '16 at 03:43
1

When using a column of type TIMESTAMP (without time-zone, which I see as equivalent to a LocalDateTime) what time-zone will JDBC use to convert that TIMESTAMP to an instant? – acorello Sep 28 '17 at 11:10
If you're storing timestamps into a database, you should be storing them as UTC. Otherwise, I think that you are right. See this [`setTimestamp` answer for more details](https://stackoverflow.com/a/14070771/706724). – pickypg Sep 28 '17 at 16:57
The example given, at least when using the Microsoft JDBC implementation, causes the *local datetime of the JVM* to be written to the DB, not a datetime in UTC. There is an override on the setTimestamp() method that takes a Calendar instance, so you can provide the timezone you want. – jmrah Nov 14 '18 at 14:49
This is dangerous advice. `Timestamp` is fundamentally `Instant` like (no timezone, UTC-epoch-millis based), and therefore, if the database is actually storing e.g. TIMESTAMP WITH TIME ZONE, which is more like ZonedDateTime, you're converting from ZDT to instant on one side and then letting j.s.Timestamp convert back to ZDT which __easily risks introducing errors due to mismatching zones__. It's also slow. Much, much, MUCH better: `rs.getObject(index/colName, LocalDateTime.class)`. Which avoids all that dangerous conversion. Replace LDT with whatever type you're looking for. – rzwitserloot Mar 04 '23 at 15:34

score 6 · Answer 2 · edited Jun 03 '18 at 17:34

No need to convert

Like others have said in comments, PostgreSQL's JDBC driver now supports JDBC 4.2 including Java 8 Time API support. We can exchange java.time objects directly with the database.

https://jdbc.postgresql.org/documentation/head/8-date-time.html

So no need to convert, no need to ever use the java.sql types again. Use only their replacements in the java.time package as shown in this list.

PostgreSQL™                      Java SE 8 (java.time)
DATE                             LocalDate
TIME [ WITHOUT TIMEZONE ]        LocalTime
TIMESTAMP [ WITHOUT TIMEZONE ]   LocalDateTime
TIMESTAMP WITH TIMEZONE          OffsetDateTime or Instant

This can be retrieved via ResultSet::getObject

ResultSet rs = ...;
while (rs.next()) {
  LocalDate localDate = rs.getObject(1, LocalDate.class));
}

Store a java.time object by calling PreparedStatement::setObject.

myPreparedStatement.setObject( … , myInstant ) ;

This Answer is a very important update. Sticking with the *java.time* classes only is indeed the way to go. — Basil Bourque, Jun 03 '18 at 17:32

How to get a java.time object from a java.sql.Timestamp without a JDBC 4.2 driver?

2 Answers2

New Methods On Old Classes

No need to convert

Linked

Related