Before writing a Java Date to an SQL TIMESTAMP col

问题:

Before writing a Java Date to an SQL TIMESTAMP column, does JDBC translate the date from the Java virtual machine time zone to that of the database session?

For example, suppose the Java virtual machine time zone is UTC and the database session time zone is UTC-5. If a Java program attempts to store 2000-01-01 00:00:00 by passing it to PreparedStatement#setTimestamp(int, Timestamp), according to the JDBC standard, will the database store TIMESTAMP '2000-01-01 00:00:00' or TIMESTAMP '1999-12-31 19:00:00'?

回答1:

No, JDBC is just an API on how the client can access the database. For timestamp storage, this will have to be dependent by the organisation that writes their database drivers that conforms to the JDBC API standard.

Here's an implementation of MySQL's implementation of PreparedStatement. They seem to take Java's JVM timezone to MySQL Timezone (check the setTimestampInternal() method).

回答2:

Now my requirement is that it should store the value in GMT/UTC irrespective of the timezone of the JVM. Is there a way to set the timezone on the fly and then to unset it once I'm done with JDBC?

Edit: Ok, I found a way around that issue. Did the following

TimeZone default = TimeZone.getDefault();
try
{
  TimeZone.setDefault(TimeZone.getTimeZone("UTC"));

  //Do stuff with JDBC
}
finally
{
  TimeZone.setDefault(default);
}

回答3:

You can use overloaded setTimestamp setter accepting Calendar instance to specify timezone

Sample (If you're using Joda datetime):

org.joda.time.DateTime sendDateUTC = new DateTime( DateTimeZone.UTC ).withMillis( millis );
statement.setTimestamp (1, sendDateUTC, sendDateUTC.toGregorianCalendar() );

As per javaDoc: Sets the designated parameter to the given java.sql.Timestamp value, using the given Calendar object. The driver uses the Calendar object to construct an SQL TIMESTAMP value, which the driver then sends to the database. With a Calendar object, the driver can calculate the timestamp taking into account a custom timezone. If no Calendar object is specified, the driver uses the default timezone, which is that of the virtual machine running the application.

void setTimestamp(int parameterIndex, java.sql.Timestamp x, Calendar cal)
    throws SQLException;

回答4:

The spec is goofy. The java.util.Date stores milliseconds from epoch in the GMT reference frame. Java.sql.Timestamp is a Date plus nanoseconds in the same reference frame. All the non-deprecated getters and setters use the GMT reference frame. For any sort of sanity, the default time zone for a storing a Timestamp should be GMT.

In a multi-tiered application, the front-end, the driver, and the database server could all be in different time zones. Some of the tiers could be in different time zones at the same time; for instance, if you are doing internet load-balancing across a continent, or if you have a mobile app connecting to a central server. A cloud operating environment would be much the same scenario where you have no idea where the JDBC driver will be running, nor any guarantee that will never change.

The only way I know to achieve consistency in these environments is to only use the parameter setter and ResultSet getter that accept a Calendar, and make sure every app that accesses the data uses the some calender, preferrably GMT or UTC.