0

In retrieving a timestamp from Postgres or Oracle -- let's call it startDate -- is it better to use the resultSet.getString("startDate") call or resultSet.getTimestamp("startDate")?

Currently I'm using resultSet.getString("startDate") and it works fine, but it occurred to me it might be better practice to use getTimestamp().

Is there an established best practice? Could there be any unexpected consequences from using ResultSet.getString() on a timestamp?

ktm5124
  • 11,861
  • 21
  • 74
  • 119
  • 1
    FYI, the troublesome old date-time classes such as `java.util.Date`, `java.util.Calendar`, and `java.text.SimpleDateFormat` are now legacy, supplanted by the [*java.time*](https://docs.oracle.com/javase/10/docs/api/java/time/package-summary.html) classes. Most of the *java.time* functionality is back-ported to Java 6 & Java 7 in the [***ThreeTen-Backport***](http://www.threeten.org/threetenbp/) project. Further adapted for earlier Android (<26) in [***ThreeTenABP***](https://github.com/JakeWharton/ThreeTenABP). See [*How to use ThreeTenABP…*](http://stackoverflow.com/q/38922754/642706). – Basil Bourque Dec 07 '18 at 00:24
  • 1
    What is the *exact* data type of your column in Postgres and in Oracle? Which of [these](https://www.postgresql.org/docs/current/datatype-datetime.html) *exactly*? And, which of [these](https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/Data-Types.html#GUID-7690645A-0EE3-46CA-90DE-C96DF5A01F8F) *exactly*? – Basil Bourque Dec 07 '18 at 06:04

2 Answers2

4

Is it better to use ResultSet.getString() or ResultSet.getTimestamp() to get a timestamp?

Neither.

It’s better to get a date-time object than a string since this better represents what the thing is and means, and should lend itself better to further operations in the Java program.

At the same time it’s far better to use the modern date-time classes from java.time than the old-fashioned Timestamp class. The latter has considerable design problems and has long been considered obsolete.

JDBC drivers are a little different, but see if you can get the correct time as either an Instant, an OffsetDateTime or if all else fails, then a LocalDateTime from your result set. In all cases use the two-argument getObject method. For example:

Instant instant = resultSet.getObject("startDate", Instant.class);

JDBC 4.2 specifies that java.time classes should be supported in this way. I believe all current SQL database engines have JDBC 4.2 compliant drivers out.

Only if you cannot use Java 8 or later, get a Timestamp as in your code. Next use the versions of the java.time classes from ThreeTen-Backport and use the DateTimeUtils class from ThreeTen-Backport for converting your Timestamp, best to en Instant, but if that happens to give you the wrong instant because of time zone trouble, then an LocalDateTime. Example:

Instant instant = 
    DateTimeUtils.toInstant(
        resultSet.getTimestamp("startDate")
    )
;
Basil Bourque
  • 303,325
  • 100
  • 852
  • 1,154
Ole V.V.
  • 81,772
  • 15
  • 137
  • 161
  • Actually, Instant should be avoided, because SQL timestamp values do not specify a timezone. For a JDBC driver to return an Instant, it must assume a timezone or get it from a JDBC URL parameter, which in my experience is unreliable. Sticking with LocalDateTime avoids the issue and guarantees accurate data, without having to rely on the timezone of the JDBC URL or server timezone or client timezone. – VGR Dec 06 '18 at 22:40
  • @VGR Your comment is incorrect. The SQL standard specifies two types of timestamp, one with respect for time zone or offset-from-UTC, and one without respect. `TIMESTAMP WITH TIME ZONE` pays attention to zone/offset info provided with inputs, and sends such info upon retrieval. In many databases such as Postgres this info on input is used to adjust into UTC, and so the retrieved value is in UTC as well. Some tools/drivers may inject their own opinion about time zone upon such a UTC value, which complicates the picture (an anti-feature IMHO). `TIMESTAMP WITHOUT TIME ZONE` has no zone/offset. – Basil Bourque Dec 06 '18 at 23:42
  • @BasilBourque True, for TIMESTAMP WITH TIME ZONE, Instant or ZonedDateTime is appropriate. But I don’t see those used often. I suppose it’s worth asking ktm5124 if that is the type in use. (And I agree with ad-hoc zone information being an anti-feature.) – VGR Dec 07 '18 at 02:23
  • 1
    @VGR So asked, in comment on Question. – Basil Bourque Dec 07 '18 at 06:05
0

I would use resultSet.getTimestamp(). Then your data type is mapped properly from the code to the database. You can convert that result into a String if you wish then. I don't think there is a time cost of calling resultSet.getString(), but generally it's more appropriate to retrieve the correct data type.

budducci
  • 11
  • 3