Oracle / JDBC：以ISO 8601格式检索TIMESTAMP WITH TIME ZONE值 [英] Oracle / JDBC: retrieving TIMESTAMP WITH TIME ZONE value in ISO 8601 format

问题描述

很多已经说过（并写在SO）的主题的一部分，但不是一个全面，完整的方式，所以我们可以有一个最终，覆盖它所有的解决方案，供大家使用。 / p>

我有一个Oracle数据库，其中存储全局事件的日期+时间+时区，因此必须保留原始TZ，并根据请求传递给客户端。理想情况下，它可以通过使用标准ISO 8601T格式工作得很好，可以很好地使用TIMESTAMP WITH TIME ZONE列类型（TSTZ）存储在Oracle中。

像'2013-01-02T03：04：05.060708 + 09：00'之类的

所有我需要做的是从数据库中检索上述值，并将其发送到客户端，而不进行任何操作。

问题是Java不支持ISO 8601（或任何其他日期+ time + nano + tz数据类型），情况更糟糕，因为Oracle JDBC驱动程序（ojdbc6.jar）对TSTZ的支持更少（与Oracle DB本身支持得很好）相反。

具体来说，这里是我不应该或不能做的：

• 从TSTZ到Java的任何映射Date


• Oracle JDBC驱动程序没有提供任何方法将TSTZ映射到Java Calendar对象（例如，通过JDBC获取Timestamp（）调用）


• JDBC getString（）可以正常工作，但Oracle JDBC驱动程序以格式返回字符串
  '2013-01-02 03： 04：05.060708 +9：00'，其不符合ISO 8601（无T，TZ中无尾随0等）。此外，此格式在Oracle JDBC驱动程序实现中是硬编码（！），它也忽略JVM语言环境设置和Oracle会话格式设置（即它忽略NLS_TIMESTAMP_TZ_FORMAT会话变量）。


• JDBC getObject （）或getTIMESTAMPTZ（）返回Oracle的TIMESTAMPTZ对象，这实际上是无用的，因为它没有任何到日历的转换（只有日期，时间和时间戳），所以再次失去TZ信息。
  

所以，这里是我剩下的选项：

1. 使用JDBC getString（）和字符串操作来修复并符合ISO 8601标准。这很容易做到，但是如果Oracle改变了内部硬编码的getString（）格式，就会有死亡的危险。此外，通过查看getString（）源代码，似乎使用getString（）也会导致一些性能损失。



2. 转换：SELECT TO_CHAR（tstz ...）EVENT_TIME ...。这工作正常，但有两个主要缺点：
   
   
   
   
   
   • 每个SELECT现在必须包括TO_CHAR调用， li>
     
   • 每个SELECT现在必须添加EVENT_TIME列alias（需要例如将结果序列化为Json自动）
     
     。
   
   



3. 使用Oracle的TIMESTAMPTZ java类，并从其内部（已记录的）字节数组结构中手动提取相关值（即实现Oracle忘记在其中实现的toString 。如果Oracle改变内部结构（不太可能），并且需要相对复杂的功能来实现和维护，这是有风险的。




4. 我希望有第四个，

想法？意见？

UPDATE

下面提供了很多想法，它看起来没有正确的方法来做到。就个人而言，我认为使用方法＃1 是最短和最可读的方式（并且维持良好的效果，不会丢失亚毫秒或基于SQL时间的查询功能）。

这是我最终决定使用的：

  String iso = rs.getString（col）.replaceFirst（，T）; 
  

感谢所有人的答复，

B。

解决方案

由于看起来没有魔法的方法做到这一点，最简单和最短的方法将是＃1。具体来说，这就是所需的所有代码：

  //转换Oracle的硬编码：'2013-01-02 03:04 ：05.060708 +9：00'
 //正确格式化ISO 8601：'2013-01-02T03：04：05.060708 +9：00'
 String iso = rs.getString（col）.replaceFirst ，T）; 
  

似乎只是添加'T'就够了，虽然完美主义者可能会放更多的化妆品可以优化，当然），例如：rs.getString（col）.replaceFirst（，T）。replaceAll（，）.replaceFirst（\ +（[0-9] ：，+ 0 $ 1：）;

B。

A lot have been said (and written on SO) on parts of the subject, but not in a comprehensive, complete way, so we can have one "ultimate, covering-it-all" solution for everyone to use.

I have an Oracle DB where I store date+time+timezone of global events, so original TZ must be preserved, and delivered to the client side upon request. Ideally, it could work nicely by using standard ISO 8601 "T" format which can be nicely stored in Oracle using "TIMESTAMP WITH TIME ZONE" column type ("TSTZ").

Something like '2013-01-02T03:04:05.060708+09:00'

All I need to do is to retrieve the above value from DB and send it to client without any manipulations.

The problem is that Java lacks support of ISO 8601 (or any other date+time+nano+tz data type) and the situation is even worse, because Oracle JDBC driver (ojdbc6.jar) has even less support of TSTZ (as opposed to Oracle DB itself where it's well supported).

Specifically, here's what I shouldn't or cannot do:

• Any mapping from TSTZ to java Date, Time, Timestamp (e.g. via JDBC getTimestamp() calls) won't work because I lose TZ.
• Oracle JDBC driver doesn't provide any method to map TSTZ to java Calendar object (this could be a solution, but it isn't there)
• JDBC getString() could work, but Oracle JDBC driver returns string in format
  '2013-01-02 03:04:05.060708 +9:00', which is not compliant with ISO 8601 (no "T", no trailing 0 in TZ, etc.). Moreover, this format is hard-coded (!) inside Oracle JDBC driver implementation, which also ignores JVM locale settings and Oracle session formatting settings (i.e. it ignores NLS_TIMESTAMP_TZ_FORMAT session variable).
• JDBC getObject(), or getTIMESTAMPTZ(), both return Oracle's TIMESTAMPTZ object, which is practically useless, because it doesn't have any conversion to Calendar (only Date, Time and Timestamp), so again, we lose TZ information.

So, here are the options I'm left with:

1. Use JDBC getString(), and string-manipulate it to fix and make ISO 8601 compliant. This is easy to do, but there's a danger to die if Oracle changes internal hard-coded getString() formatting. Also, by looking at the getString() source code, seems like using getString() would also result in some performance penalty.

2. Use Oracle DB "toString" conversion: "SELECT TO_CHAR(tstz...) EVENT_TIME ...". This works fine, but has 2 major disadvatages:

   • Each SELECT now has to include TO_CHAR call which is a headache to remember and write
   • Each SELECT now has to add EVENT_TIME column "alias" (needed e.g. to serialize the result to Json automatically)
     .

3. Use Oracle's TIMESTAMPTZ java class and extract relevant value manually from its internal (documented) byte array structure (i.e. implement my own toString() method which Oracle forgot to implement there). This is risky if Oracle changes internal structure (unlikely) and demands relatively complicated function to implement and maintain.

4. I hope there's 4th, great option, but from looking all over the web and SO - I can't see any.

Ideas? Opinions?

UPDATE

A lot of ideas have been given below, but it looks like there is no proper way to do it. Personally, I think using method #1 is the shortest and the most readable way (and maintains decent performance, without losing sub-milliseconds or SQL time-based query capabilities).

This is what I eventually decided to use:

String iso = rs.getString(col).replaceFirst(" ", "T");

Thanks for good answers everyone,
B.

解决方案

Since it looks like there's no magical way of doing this right, the simplest and the shortest method would be #1. Specifically, this is all the code needed:

// convert Oracle's hard-coded: '2013-01-02 03:04:05.060708 +9:00'
// to properly formatted ISO 8601: '2013-01-02T03:04:05.060708 +9:00'
String iso = rs.getString(col).replaceFirst(" ", "T"); 

it seems that just adding 'T' is enough, although a perfectionist would probably put more cosmetics (regex can optimized, of course), e.g.: rs.getString(col).replaceFirst(" ", "T").replaceAll(" ", "").replaceFirst("\+([0-9])\:", "+0$1:");

B.

这篇关于Oracle / JDBC：以ISO 8601格式检索TIMESTAMP WITH TIME ZONE值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！