JDBC在将Date插入Oracle DB之前是否会对其进行调整?我该如何预防? [英] Does JDBC adjust the Date before it inserts it into Oracle DB? How do I prevent this?

问题描述

假设我们有以下创建日期的代码:

Say we have the following code that creates date:

SimpleDateFormat sdf = new SimpleDateFormat( "dd/MM/yyyy" );
sdf.setTimeZone( TimeZone.getTimeZone( "UTC" ) ); // we know the date being parsed is UTC
Date bizDate = sdf.parse( "12/12/2015" ); // milliseconds: 1449878400000
log.info( "ms: {}", bizDate.getTime() );
log.info( "date: {}", bizDate );
... // save to db

如果该代码在UTC的Oracle DB上的UTC的JVM上运行，我将得到:

If that code runs on a JVM in UTC on an Oracle DB in UTC, I'm getting:

JVM参数:-Duser.timezone = UTC

JVM params: -Duser.timezone=UTC

millisecond: 1449878400000
date: Sat Dec 12 00:00:00 UTC 2015
in oracle db: 2015-Dec-12 00:00:00 // zero time

对于未设置为UTC(例如SGT)的JVM，我得到:

For JVM not set to UTC (e.g. SGT) I'm getting:

JVM参数:无(默认时区为SGT或UTC + 8:00)

JVM params: none (default timezone is SGT or UTC+8:00)

millisecond: 1449878400000
date: Sat Dec 12 08:00:00 SGT 2015
in oracle db: 2015-Dec-12 08:00:00 // plus 8 hours

请注意，它们的毫秒数相同，但是它们在数据库中的插入方式不同.

Notice that they both have the same milliseconds but they were inserted differently in the DB.

我的问题是:

1. JDBC标准是否说它会在插入日期之前调整Date对象?请引用您的消息来源.

1. Does JDBC standard say it adjusts the Date objects before inserting it? Please cite your source.

如果在JVM的时区未设置为UTC的情况下，JDBC在将Date对象插入数据库之前确实进行了调整，那么为什么要这样设计?我觉得这让事情变得更加混乱.我期望它将按原样插入.想象一下，如果您使用毫秒创建一个日期(例如，新的Date(1449878400000L))，并且该日期将以不同的方式存储，并且您没有关于将在其中运行代码的JVM时区的信息.或者想象您的代码将在设置为的多个JVM上运行不同的时区.

If JDBC does really adjust the Date objects before inserting it into the DB when your JVM's timezone is not set to UTC, why was it designed that way? I feel like that's making it more confusing. I was expecting it will insert it as it is. Imagine if you create a date using milliseconds (e.g. new Date( 1449878400000L ) ) and it will be stored differently and you have no information about the JVM's timezone your code will be running in. Or imagine your code will be running on multiple JVMs set to different timezones.

当JVM的时区设置为UTC以外的其他值时，如何防止JDBC调整日期?我正在使用ibatis，并且我可能无法直接访问PreparedStatements .

How do I prevent JDBC from adjusting the date when JVM's timezone is set to anything other than UTC? I'm using ibatis and I may not have direct access to PreparedStatements.

我将SimpleDateFormat的时区设置为UTC，因为我想将被解析的日期视为UTC(或根据需要视为其他时区).如果没有这样的要求，那将不是问题.现在看来，在插入它之前，我需要调整日期以反转JDBC所做的事情.

I have set the SimpleDateFormat's timezone to UTC because I want to treat the date being parsed as UTC (or as other timezone as required). It would have not been a problem had there been no such requirement. Now it seems I need to adjust the Date to reverse what JDBC is doing before inserting it.

推荐答案

问题是Java Date对象不存储时区.该值在UTC中始终为 ，并在给定的时区(通常是JVM的默认时区)中进行解析和格式化.

The problem is that Java Date objects don't store a time zone. The value is always in UTC, and is parsed and formatted in a given time zone, usually the JVM's default time zone.

Oracle DATE列也没有时区存储，但应表示用户看到的日期.在99.99％的情况下，这表示JVM默认时区中的日期.

Oracle DATE columns are also stored without time zone, but should represent the date as seen by the user. In 99.99% of cases, that means the date in the JVM's default time zone.

因此，JDBC驱动程序采用UTC中的Timestamp/Date值，将其转换为默认时区，并将其保存到数据库中.

So, the JDBC driver takes the Timestamp / Date value, which is in UTC, converts it to the default time zone, and saves that to the database.

您正在使用

You are using the PreparedStatement.setTimestamp(int parameterIndex, Timestamp x) method. To control the time zone, use the PreparedStatement.setTimestamp(int parameterIndex, Timestamp x, Calendar cal) method. Quoting the javadoc:

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

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.

这篇关于JDBC在将Date插入Oracle DB之前是否会对其进行调整?我该如何预防?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！