在将Java Date写入SQL TIMESTAMP列之前，JDBC是否将日期从JVM时区转换为数据库会话时区？

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?

在将Java Date写入SQL TIMESTAMP列之前，JDBC是否将日期从Java虚拟机时区转换为数据库会话的日期？

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'?

例如，假设Java虚拟机时区为UTC且数据库会话时区为UTC-5。如果Java程序通过将其传递给PreparedStatement＃setTimestamp（int，Timestamp）来尝试存储2000-01-01 00:00:00，则根据JDBC标准，数据库将存储TIMESTAMP'2000-01-01 00:00 ：00'或TIMESTAMP'1999-12-31 19:00:00'？

4 个解决方案

#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.

不，JDBC只是客户端如何访问数据库的API。对于时间戳存储，这必须由编写符合JDBC API标准的数据库驱动程序的组织依赖。

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).

这是MySQL的PreparedStatement实现的实现。他们似乎将Java的JVM时区带到了MySQL Timezone（检查setTimestampInternal（）方法）。

#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?

现在我的要求是它应该以GMT / UTC存储值，而不管JVM的时区。有没有办法在运行时设置时区，然后在完成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

您可以使用重载的setTimestamp setter接受Calendar实例来指定时区

Sample (If you're using Joda datetime):

示例（如果您使用的是Joda日期时间）：

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.

根据javaDoc：使用给定的Calendar对象将指定参数设置为给定的java.sql.Timestamp值。驱动程序使用Calendar对象构造SQL TIMESTAMP值，然后驱动程序将该值发送到数据库。使用Calendar对象，驱动程序可以计算考虑自定义时区的时间戳。如果未指定Calendar对象，则驱动程序将使用默认时区，即运行应用程序的虚拟机的时区。

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

#4

-1

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.

规范很傻。 java.util.Date存储GMT参考帧中纪元的毫秒数。 Java.sql.Timestamp是同一参考帧中的Date加纳秒。所有未弃用的getter和setter都使用GMT参考框架。对于任何类型的健全性，存储时间戳的默认时区应为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.

在多层应用程序中，前端，驱动程序和数据库服务器都可以位于不同的时区。有些层可能同时位于不同的时区;例如，如果您在整个大陆进行互联网负载平衡，或者您有一个移动应用程序连接到*服务器。云操作环境与您不知道JDBC驱动程序将在何处运行的情况大致相同，也不保证永远不会改变。

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.

我知道在这些环境中实现一致性的唯一方法是仅使用接受日历的参数setter和ResultSet getter，并确保访问数据的每个应用程序都使用某些日历，最好是GMT或UTC。

#1