Does the timezone matter when parsing a Timestamp?

Question

I have a timestamp encoded as a String—for example, "2012-02-12T09:08:13.123456-0400", coming from an Oracle database.

The only way that I can think of reading this timestamp, is by using Timestamp.valueOf(), and that requires a format of yyyy-[m]m-[d]d hh:mm:ss[.f...]

I am convinced that this is the only way to read time without losing precision because other ways do not support nanosecond precision included in the example above (".123456").

With that in mind, I can simply trim the needed values, to fit the required format. Hence, the original string would be transformed:

• Before: "2012-02-12T09:08:13.123456-0400"
• After: "2012-02-12 09:08:13.123456"

If I do this, I remove the "-0400" timezone offset. This comes as a red flag to me, until I saw this post. One of the proposed answers states,

I think the correct answer should be java.sql.Timestamp is NOT timezone specific. Timestamp is a composite of java.util.Date and a separate nanoseconds value. There is no timezone information in this class. Thus just as Date this class simply holds the number of milliseconds since January 1, 1970, 00:00:00 GMT + nanos.

To prove to myself that the offset is not needed, I wrote a simple integration test.

Insert this timestamp into the database: "2015-09-08 11:11:12.123457". Read the database using Java, and print out the details. I get "2015-09-08 11:11:12.123457", which is the same value. This happens to be ok, since my JVM and the Oracle DB are running on the same machine.

1. Is it a fact that a timezone is not factor in java.sql.Timestamp?
2. Is there a better way to read that entire timestamp, without losing any precision in Java 7?

Answer 1

tl;dr

org.threeten.bp.OffsetDateTime odt = 
    OffsetDateTime.parse(
        "2012-02-12T09:08:13.123456-0400",
        org.threeten.bp.format.DateTimeFormatter.ofPattern( "yyyy-MM-dd'T'HH:mm:ssZ" )  // Specify pattern as workaround for Java 8 bug in failing to parse if optional colon is not present.
    )
;

Using java.time

Rather than receiving a String from your database, you should retrieve an object, a date-time object, specifically a java.time object.

The java.time classes supplant the troublesome old date-time classes including java.sql.Timestamp. If your JDBC driver supports JDBC 4.2 and later, you can pass and receive java.time objects directly.

Instant

The Instant class represents a moment on the timeline in UTC with a resolution of nanoseconds (up to nine (9) digits of a decimal fraction). So this is equivalent to java.sql.Timestamp including support for the six digits of microseconds of your input data, so no precision lost per the requirements of your Question.

Instant instant = myResultSet.getObject( … , Instant.class ) ;

instant.toString(): 2012-02-12T13:08:13.123456Z

ZonedDateTime

If you want to see that same moment through the lens of a particular region's wall-clock time, apply a ZoneId to get a ZonedDateTime object.

ZoneId z = ZoneId.of( "America/St_Thomas" ) ;
ZonedDateTime zdt = instant.atZone( z ) ;

zdt.toString(): 2012-02-12T09:08:13.123456-04:00[America/St_Thomas]

OffsetDateTime

As for your direct Question of how to make sense of the string 2012-02-12T09:08:13.123456-0400 as a date-time value, parse as an OffsetDateTime.

A time zone has a name in the format of continent/region, and represents a history of past, present, and future changes to a region’s offset caused by anomalies such as Daylight Saving Time (DST). We have clue as to the time zone with your string, so we use OffsetDateTime rather than ZonedDateTime.

OffsetDateTime odt = OffsetDateTime.parse( "2012-02-12T09:08:13.123456-0400" ) ;

Well, that line of code above should have worked, but in Java 8 there is a small bug in parsing the offset lacking the optional COLON character between the hours and minutes. So -04:00 in Java 8 will parse but not -0400. Bug fixed in Java 9. Your String is indeed compliant with the ISO 8601 standard for date-time formats used by default in the java.time classes. Tip: Generally best to always format your offsets with the colon, and both hours/minutes and with a padding zero – I've seen other protocols and libraries expect only such full format.

Until you move to Java 9, specify the formatting pattern explicitly rather than rely on the implicit default pattern, as a workaround for this bug.

OffsetDateTime odt = 
    OffsetDateTime.parse(
        "2012-02-12T09:08:13.123456-0400",
        DateTimeFormatter.ofPattern( "yyyy-MM-dd'T'HH:mm:ssZ" )  // Specify pattern as workaround for Java 8 bug in failing to parse if optional colon is not present.
    )
;

Converting

If your JDBC driver is not yet compliant with JDBC 4.2, retrieve a java.sql.Timestamp object, for use only briefly. Immediately convert to java.time using new methods added to the old date-time classes.

java.sql.Timestamp ts = myResultSet.getTimestamp( … ) ;
Instant instant = ts.toInstant();

Proceed to do your business logic in java.time classes. To send a date-time back to the database convert from Instant to java.sql.Timestamp.

myPreparedStatement.setTimestamp( … , java.sql.Timestamp.from( instant ) ) ;

Java 6 & 7

In Java 6 & 7, the above concepts still apply, but java.time is not built-in. Use the ThreeTen-Backport library instead. To obtain, see bullets below.

In Java 7, you cannot use JDBC 4.2 features. So we cannot directly access java.time objects from the database through the JDBC driver. As seen above, we must convert briefly into java.sql.Timestamp from Instant. Call the utility methods DateTimeUtils.toInstant(Timestamp sqlTimestamp) & DateTimeUtils.toSqlTimestamp(Instant instant).

java.sql.Timestamp ts = myResultSet.getTimestamp( … ) ;
Instant instant = DateTimeUtils.toInstant( ts ) ;

…and…

java.sql.Timestamp ts = DateTimeUtils.toSqlTimestamp( instant ) ;
myPreparedStatement.setTimestamp( … , ts ) ;

---

About java.time

The java.time framework is built into Java 8 and later. These classes supplant the troublesome old legacy date-time classes such as java.util.Date, Calendar, & SimpleDateFormat.

The Joda-Time project, now in maintenance mode, advises migration to the java.time classes.

To learn more, see the Oracle Tutorial. And search Stack Overflow for many examples and explanations. Specification is JSR 310.

Where to obtain the java.time classes?

• Java SE 8, Java SE 9, and later
  • Built-in.
  • Part of the standard Java API with a bundled implementation.
  • Java 9 adds some minor features and fixes.
• Java SE 6 and Java SE 7
  • Much of the java.time functionality is back-ported to Java 6 & 7 in ThreeTen-Backport.
• Android
  • The ThreeTenABP project adapts ThreeTen-Backport (mentioned above) for Android specifically.
  • See How to use ThreeTenABP….

The ThreeTen-Extra project extends java.time with additional classes. This project is a proving ground for possible future additions to java.time. You may find some useful classes here such as Interval, YearWeek, YearQuarter, and more.