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.

Answer 2

java.sql.Timestamp.valueOf(String) parses the provided time in the current timezone. You can check this by looking at the implementation, which in the end simply calls Timestamp(int year, int month, int date, int hour, int minute, int second, int nano) which calls public Date(int year, int month, int date, int hrs, int min, int sec), which says (emphasis mine):

Allocates a Date object and initializes it so that it represents the instant at the start of the second specified by the year, month, date, hrs, min, and sec arguments, in the local time zone.

It is true that Timestamp doesn't have time zone information (it is just a wrapper with number of seconds since the GMT epoch + nanoseconds), but when loading or storing a Timestamp, JDBC will use the local (default) timezone, unless explicitly declared otherwise.

This means that a Timestamp at 10:00 in your local timezone will end up in the database as an SQL TIMESTAMP at 10:00, and not at - for example - 08:00 if your timezone is 2 hours ahead of GMT; and the reverse when loading.

Answer 3

You are correct that java.sql.Timestamp has no timezone information. Under the covers it is just an instant that is milliseconds since the epoch and the epoch itself is defined with essentially a 0 offset, January 1, 1970, 00:00:00 GMT.

That being said, when working with times, offset always matters and this will lead you into the hell, er, "dynamic world" that is ISO-8601 in Java 8 java.time. The offset matters a lot, unless you are standing in Greenwich, England. If you try to just ignore it you may sometimes guess the day wrong; it is Monday in Greenwich 8 hours earlier than it is Monday in Seattle, for example. I don't think Java8 has a built in DateTimeFormatter for the -0000 variants of ISO-8601, but if you are sure your format is "stable", this would work.

public void java8TimeWTF() {
  OffsetDateTime odt = OffsetDateTime.parse(
    "2012-02-12T09:08:13.123456-0400",
    DateTimeFormatter.ofPattern("yyyy-MM-dd'T'HH:mm:ss.nZ"));
  Instant i = odt.toInstant();
  System.out.printf("odt: %s, i: %s\n", odt, i);
}

Outputs odt: 2012-02-12T09:08:13.000123456-04:00, i: 2012-02-12T13:08:13.000123456Z

Here's what we had to do to deal with all the variant's of ISO-8601 coming out of our clients

import org.testng.annotations.Test;
import java.time.Instant;
import java.time.OffsetDateTime;
import java.time.format.DateTimeFormatter;
import java.time.format.DateTimeParseException;
import static org.testng.Assert.assertEquals;

public class Java8Time8601 {
  private final long EXPECTED_MILLIS = 1493397412000L;

  public Instant iso8601ToInstant(String s) {
    DateTimeFormatter[] dateTimeFormatters = {
            DateTimeFormatter.ISO_INSTANT,
            DateTimeFormatter.ISO_OFFSET_DATE_TIME,
            DateTimeFormatter.ofPattern("yyyy-MM-dd'T'HH:mm:ssZ")
    };
    for (DateTimeFormatter dtf : dateTimeFormatters) {
      try {
        OffsetDateTime odt = OffsetDateTime.parse(s, dtf);
        Instant i = odt.toInstant();
        return i;
      } catch (DateTimeParseException dtpe) {
        ;
      }
    }
    throw new IllegalArgumentException(String.format("failed to parse %s", s));
  }

  @Test
  public void testInstantParse8601_Z() throws Exception {
    String[] candidates = {
            "2017-04-28T16:36:52.000Z",
            "2017-04-28T16:36:52.00Z",
            "2017-04-28T16:36:52.0Z",
            "2017-04-28T16:36:52Z",
            "2017-04-28T16:36:52+00:00",
            "2017-04-28T16:36:52-00:00",
            "2017-04-28T09:36:52-07:00",
            "2017-04-28T09:36:52-0700",
    };
    for (String candidate : candidates) {
      Instant i = iso8601ToInstant(candidate);
      assertEquals(i.toEpochMilli(), EXPECTED_MILLIS, String.format("failed candidate %s", candidate));
      System.out.println(i.toString());
    }
  }
}

There has got to be a better way.