使用 Java、iBatis 和 Oracle 处理 UTC 日期时间的正确方法是什么？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1653402/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What's the right way to handle UTC date-times using Java, iBatis, and Oracle?
提问by Jim Ferrans
I'm coming up against an unexpected daylight savings time problem in code I thought was purely UTC. I'm using Java 1.6, the iBatis SQL mapper (2.3.3), and Oracle XE (an eval version of Oracle 10.2) with the Oracle thin driver.
我在我认为纯粹是 UTC 的代码中遇到了一个意想不到的夏令时问题。我将 Java 1.6、iBatis SQL 映射器 (2.3.3) 和 Oracle XE（Oracle 10.2 的评估版）与 Oracle 瘦驱动程序一起使用。
The database contains a table that represents a television broadcast schedule. Each "Asset" (program) has a start_time and and end time. Here's the relevant slice:
该数据库包含一个表示电视广播时间表的表。每个“资产”（程序）都有一个开始时间和结束时间。这是相关的切片：
create table Asset
(
asset_id integer not null, -- The unique id of the Asset.
[...]
start_time timestamp, -- The start time.
end_time timestamp, -- The end time.
[...]
constraint asset_primary_key primary key (asset_id),
constraint asset_time check (end_time >= start_time)
);
The oracle asset_timeconstraint is firing for programs that straddle the US central daylight savings time adjustment this upcoming Sunday morning, 11/1/2009.
在asset_time即将到来的 2009 年 1 月 11 日上午，Oracle约束对跨越美国中央夏令时调整的程序产生影响。
I have this data transfer object (the Dates are java.util.Dates):
我有这个数据传输对象（日期是 java.util.Dates）：
public class Asset
{
protected Long asset_id;
[...]
protected Date start_time;
protected Date end_time;
public Date getStart_time() { return start_time; }
public Date getEnd_time() { return end_time; }
public void setStart_time(Date start_time) { this.start_time = start_time; }
public void setEnd_time(Date end_time) { this.end_time = end_time; }
[...]
}
And in the iBatis SQL map I have this statement that inserts an Asset DTO into the Oracle Asset table:
在 iBatis SQL 映射中，我有以下语句将资产 DTO 插入到 Oracle 资产表中：
<insert id="Asset.insert" parameterClass="com.acme.Asset">
insert into Asset
( asset_id, [...] start_time, end_time )
values
( #asset_id#, [...] #start_time#, #end_time# )
</insert>
On the Java side I've verified that I'm giving iBatis the correct UTC date input via this pre-insert assertion, which isn'tthrown:
在 Java 方面，我已验证我通过此预插入断言为 iBatis 提供了正确的 UTC 日期输入，该断言并未抛出：
System.err.println("Inserting asset " + program_id);
System.err.println(" "+asset.getStart_time_str()+"--"+asset.getEnd_time_str());
if ( !asset.getEnd_time().after(asset.getStart_time())) {
System.err.println("Invalid datetime range in asset.");
throw new AssertionError("Invalid datetime range in asset.");
}
Just before the Oracle constraint failure the above code prints:
就在 Oracle 约束失败之前，上面的代码打印：
Inserting asset EP011453960004
2009-11-01T06:30:00Z--2009-11-01T07:00:00Z
I'm in the US central time zone, GMT -5:00, so this program starts at 1:30am and ends at 2:00am. The daylight savings change hits at 2:00am and turns the clock back to 1:00am.
我在美国中部时区，格林威治标准时间 -5:00，所以这个程序从凌晨 1:30 开始，在凌晨 2:00 结束。夏令时更改在凌晨 2:00 发生，并将时钟调回到凌晨 1:00。
iBatis reports the Oracle constraint failure (edited):
iBatis 报告 Oracle 约束失败（已编辑）：
2009-10-30 22:58:42,238 [...] Executing Statement:
insert into Asset ( asset_id, [...] start_time, end_time )
values ( ?, [...] ?, ? )
2009-10-30 22:58:42,238 [...] Parameters:
[EP011453960004, [...] 2009-11-01 01:30:00.0, 2009-11-01 01:00:00.0]
2009-10-30 22:58:42,238 [..] Types:
[java.lang.Long, [...] java.sql.Timestamp, java.sql.Timestamp]
2009-10-30 22:58:42,285 [...] - Failed with a SQLException:
--- The error occurred in com/acme/data/dao/Asset-Write.xml.
--- The error occurred while applying a parameter map.
--- Check the Asset.insert-InlineParameterMap.
--- Check the statement (update failed).
--- Cause: java.sql.SQLException: ORA-02290: check constraint (ACME.ASSET_TIME)
violated
You'll notice that on the Oracle side, it's seeing the start_time/end_time with the daylight savings time adjustment, so something in the iBatis mapping logic or the Oracle driver isn't doing what I expected. The driver is ojdbc14.jar, the thin driver:
您会注意到，在 Oracle 端，它看到 start_time/end_time 与夏令时调整，因此 iBatis 映射逻辑或 Oracle 驱动程序中的某些内容没有按照我的预期执行。驱动是ojdbc14.jar，瘦驱动：
JDBCReadWrite.Driver = oracle.jdbc.OracleDriver
JDBCReadWrite.ConnectionURL = jdbc:oracle:thin:@localhost:1521:XE
What's the correct way to ensure that this code is purely UTC?
确保此代码纯粹是 UTC 的正确方法是什么？
Thanks in advance!
提前致谢！
回答by Jim Ferrans
I have a solution which seems to do the trick. Even though the application and the database used types that store time offsets from midnight on 1/1/1970 in GMT, the JDBC specification calls for applying an adjustment from/to the JVM's default timezone going in/out. And iBatis maps dates using the JDBC default. The adjustments were always symmetrical and therefore harmless as long as the data didn't cross a daylight savings time boundary, or if the machine or JVM were set to GMT by default.
我有一个似乎可以解决问题的解决方案。尽管应用程序和数据库使用的类型存储从格林威治标准时间 1970 年 1 月 1 日午夜开始的时间偏移量，但 JDBC 规范要求对进出 JVM 的默认时区进行调整。并且 iBatis 使用 JDBC 默认映射日期。只要数据不跨越夏令时边界，或者机器或 JVM 默认设置为 GMT，调整总是对称的，因此是无害的。
As an experiment I switched the JVM default timezone to GMT:
作为一个实验，我将 JVM 默认时区切换到 GMT：
TimeZone.setDefault(TimeZone.getTimeZone("UTC"));
and this solved the problem, though in a very heavy-handed way (other code in the JVM may not expect this).
这解决了这个问题，尽管以一种非常严厉的方式（JVM 中的其他代码可能没有预料到这一点）。
But iBatis allows you to override the default type handling, at any level of granularity. I wrote a GMT-preserving type handler and registered it for all my java.util.Dates:
但是 iBatis 允许您在任何粒度级别覆盖默认类型处理。我编写了一个 GMT-preserving 类型处理程序，并为我所有的 java.util.Dates 注册了它：
<typeHandler callback="com.acme.GMTDateTypeHandler" javaType="java.util.Date"/>
My type handler looks like this:
我的类型处理程序如下所示：
public class GMTDateTypeHandler implements TypeHandlerCallback
{
@Override
public void setParameter(ParameterSetter setter, Object parameter)
throws SQLException
{
java.util.Date date = (java.util.Date) parameter;
if ( date == null )
setter.setNull(Types.TIMESTAMP);
else
{
Timestamp timestamp = new Timestamp(date.getTime());
Calendar calendar = Calendar.getInstance(TimeZone.getTimeZone("UTC"));
setter.setTimestamp(timestamp, calendar);
}
}
@Override
public Object getResult(ResultGetter getter) throws SQLException
{
Calendar calendar = Calendar.getInstance(TimeZone.getTimeZone("UTC"));
return getter.getTimestamp(calendar);
}
@Override
public Object valueOf(String s)
{
throw new UnsupportedOperationException(
"GMTDateTypeHandler.valueOf() is not supported.");
}
}
回答by Juergen Hartelt
Usually, Oracle converts date/time values from the client timezone to the server timezone, when storing data. And backwards, when reading it out again.
通常，Oracle 在存储数据时会将日期/时间值从客户端时区转换为服务器时区。并倒退，当再次阅读时。
If you want the date/time values to be stored un-altered, you might want to use a variant of the timezone data type, the "TIMESTAMP WITH TIME ZONE Datatype", which lets you store the time zone with the value. You can find some info here in the Oracle SQL data type doc. Just search for the "with timezone" part.
如果您希望不更改日期/时间值存储，您可能需要使用时区数据类型的变体，即“TIMESTAMP WITH TIME ZONE Datatype”，它允许您使用值存储时区。您可以在Oracle SQL 数据类型文档中找到一些信息 。只需搜索“带时区”部分。
