Timestamp conversion to instant in Java adds unnecessary time offset

Question

I need to be able to convert data fetched from MySQL database that is stored in a "datetime" fields into Java ZonedDateTime object.

ZonedDateTime dt = ZonedDateTime.ofInstant(rs.getTimestamp("Start").toInstant(), UTC_ZONE_ID)

The problem I'm having is that toInstant() adds local time offset to the Timestamp object which I don't need because the datetime is already stored in a UTC format in a database. So when I run the following code:

ZonedDateTime startDT = 
        ZonedDateTime.ofInstant(rs.getTimestamp("Start").toInstant(),Globals.LOCALZONEID);
System.out.println(rs.getTimestamp("start"));
System.out.println(rs.getTimestamp("start").toInstant());

I get:

2017-06-08 13:15:00.0
2017-06-08T17:15:00Z

I need the time component to stay unchanged.

I was unable to find any obvious solution to the problem so am I missing something here?

Answer 1

Timestamp & Instant always in UTC

The problem I'm having is that .toInstant() add local time offset to the Timestamp object

No it does not.

• A java.sql.Timestamp is in UTC by definition.
• A Instant is in UTC by definition.

Neither can have any other zone assigned.

Do not bunch up your code into a single line. Break out each step into separate lines so you can debug their values.

java.sql.Timestamp ts = rs.getTimestamp("Start") ;  // Actually in UTC, but it's `toString` method applies JVM’s current default time zone while generating string.
Instant instant = ts.toInstant() ;                  // Same moment, also in UTC.
ZoneId z = ZoneId.of( "America/Montreal" ) ;        // Or call your global var: `Globals.LOCALZONEID`.
ZonedDateTime zdt = instant.atZone( z );            // Same moment, same point on timeline, but with wall-clock time seen in a particular zone.

After that, you may see the issue (or non-issue). If not, edit your question to show the debug values of each of these variables.

Do not trust Timestamp::toString

Important: The java.sql.Timestamp::toString method lies. That method applies your JVM’s current default time zone while generating the string. The actual value is always in UTC. One of many reasons to avoid these troublesome legacy classes. Run the following code example on your own machine to see the influence of your default time zone on the textual representation of the Timestamp.

Let’s run a simulation of that code running live in IdeOne.com. The JVM at IdeOne.com defaults to UTC/GMT, so we override default by specifying the default as Pacific/Auckland arbitrarily.

Instant now = Instant.now() ;                       // Simulating fetching a `Timestamp` from database by using current moment in UTC.

TimeZone.setDefault( TimeZone.getTimeZone( "Pacific/Auckland" ) ) ;
ZoneId zoneIdDefault = ZoneId.systemDefault() ;
ZoneOffset zoneOffset = zoneIdDefault.getRules().getOffset( now ) ;

java.sql.Timestamp ts = java.sql.Timestamp.from( now ) ;  // Actually in UTC, but it's `toString` method applies JVM’s current default time zone while generating string.
Instant instant = ts.toInstant() ;                  // Same moment, also in UTC.
ZoneId z = ZoneId.of( "America/Montreal" ) ;        // Or call your global var: `Globals.LOCALZONEID`.
ZonedDateTime zdt = instant.atZone( z );            // Same moment, same point on timeline, but with wall-clock time seen in a particular zone.

Current default time zone: Pacific/Auckland

Current default offset-from-UTC: Pacific/Auckland | total seconds: 43200

now.toString(): 2017-06-09T04:41:10.750Z

ts.toString(): 2017-06-09 16:41:10.75

instant.toString(): 2017-06-09T04:41:10.750Z

z.toString(): America/Montreal

zdt.toString(): 2017-06-09T00:41:10.750-04:00[America/Montreal]

Avoid legacy date-time classes

The old date-time classes found outside the java.time package are troublesome, confusing, badly designed, and flawed. Avoid them whenever possible. This includes java.sql.Timestamp.

Your JDBC 4.2 compliant driver can directly address java.time types by calling PreparedStatement::setObject and ResultSet::getObject.

myPreparedStatement.setObject( … , instant ) ;

… and …

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

If using a JDBC driver not yet updated to JDBC 4.2 and java.time, convert briefly to java.sql.Timestamp using new methods added to the old class: from ( Instant ), toInstant(), and such. But beyond exchanging data with the database, do all your real work (business logic) in java.time objects.

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

… and …

Instant instant = myResultSet.getTimestamp( … ).toInstant() ;

---

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.

You may exchange java.time objects directly with your database. Use a JDBC driver compliant with JDBC 4.2 or later. No need for strings, no need for java.sql.* classes.

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
  • Later versions of Android bundle implementations of the java.time classes.
  • For earlier Android (<26), the ThreeTenABP project adapts ThreeTen-Backport (mentioned above). 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

The old classes like java.sql.Timestamp have quite some issues with their design. One of the things that often cause confusion is that Timestamp.toString() prints the time in the JVM’s time zone even though the time Timestamp in it just holds a point in time with no time zone. To be concrete, when a Timestamp is equal to 2017-06-08T17:15:00Z in UTC and you print it on a computer running Pittsburg time (which at this time of year has an offset of -4:00 from UTC), the Timestamp.toString() is implicitly called, it reads the JVM’s time zone and prints the time as 13:15:00.0 just because this time in Pennsylvania is equal to the UTC time in the Timestamp.

So to cut a long story short, do not worry, both your Timestamp and your Instant are correct.

Answer 3

I think this code snippet answers your question. This takes in a String in a local time zone, converts it to UTC, and stores it in a db.

    //Getting the LocalDateTime Objects from String values
    DateTimeFormatter df = DateTimeFormatter.ofPattern("yyyy-MM-dd kk:mm"); 
    String txtStartTime = "2017-03-29 12:00";

    LocalDateTime ldtStart = LocalDateTime.parse(txtStartTime, df);


    //Convert to a ZonedDate Time in UTC
    ZoneId zid = ZoneId.systemDefault();

    ZonedDateTime zdtStart = ldtStart.atZone(zid);
    System.out.println("Local Time: " + zdtStart);
    ZonedDateTime utcStart = zdtStart.withZoneSameInstant(ZoneId.of("UTC"));
    System.out.println("Zoned time: " + utcStart);
    ldtStart = utcStart.toLocalDateTime();
    System.out.println("Zoned time with zone stripped:" + ldtStart);
    //Create Timestamp values from Instants to update database
    Timestamp startsqlts = Timestamp.valueOf(ldtStart); //this value can be inserted into database
    System.out.println("Timestamp to be inserted: " +startsqlts);

    //insertDB(startsqlts);