Oracle/JDBC:以 ISO 8601 格式检索 TIMESTAMP WITH TIME ZONE 值

2022-01-16 00:00:00 datetime database timezone jdbc java

关于该主题的某些部分已经说了很多(并写在 SO 上)，但不是以全面、完整的方式，因此我们可以有一个终极的、包罗万象的"解决方案供所有人使用.

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.

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

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

类似于 '2013-01-02T03:04:05.060708+09:00'

我需要做的就是从数据库中检索上述值并将其发送给客户端，无需任何操作.

问题是Java缺乏对ISO 8601(或任何其他日期+时间+nano+tz数据类型)的支持，情况更糟，因为Oracle JDBC驱动程序(ojdbc6.jar)对TSTZ的支持更少(与得到很好支持的 Oracle DB 本身相反).

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:

  • 任何从 TSTZ 到 java Date、Time、Timestamp 的映射(例如通过 JDBC getTimestamp() 调用)都不起作用，因为我丢失了 TZ.
  • Oracle JDBC 驱动程序不提供将 TSTZ 映射到 java Calendar 对象的任何方法(这可能是一个解决方案，但它不存在)
  • 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 对象，这实际上是没有用的，因为它没有任何转换为​​ Calendar(只有 Date、Time 和 Timestamp)，所以我们再次丢失了 TZ 信息.

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

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

  1. 使用 JDBC getString() 并对其进行字符串操作以修复并使其符合 ISO 8601.这很容易做到，但如果 Oracle 更改内部硬编码的 getString() 格式，就会有死亡的危险.此外，通过查看 getString() 源代码，似乎使用 getString() 也会导致一些性能损失.

  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.

使用 Oracle DBtoString"转换:SELECT TO_CHAR(tstz...) EVENT_TIME ...".这很好用，但有两个主要缺点:

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

  • 现在每个 SELECT 都必须包含 TO_CHAR 调用，这让人头疼的记住和编写
  • 现在每个 SELECT 都必须添加 EVENT_TIME 列别名"(例如需要自动将结果序列化为 Json)
    .

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

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.

想法?意见?

更新

下面给出了很多想法，但似乎没有合适的方法去做.就我个人而言，我认为使用方法#1 是最短且最易读的方法(并且保持了不错的性能，不会丢失亚毫秒或基于 SQL 时间的查询能力).

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");

感谢大家的好回答，
B.

Thanks for good answers everyone,
B.

推荐答案

既然看起来没有什么神奇的方法可以做到这一点，那么最简单最短的方法就是#1.具体来说，这就是所有需要的代码:

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");

似乎只添加'T'就足够了，尽管完美主义者可能会添加更多化妆品(当然，正则表达式可以优化)，例如:rs.getString(col).replaceFirst(" ", "T").replaceAll(" ", "").replaceFirst("+([0-9]):", "+0$1:");

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.

相关文章