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

Oracle / JDBC: retrieving TIMESTAMP WITH TIME ZONE value in ISO 8601 format

提问by Borka

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.

关于该主题的某些部分已经说了很多（并写在 SO 上），但不是以全面、完整的方式，所以我们可以有一个“最终的、涵盖所有内容的”解决方案供每个人使用。

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

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

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

类似于“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).

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

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.

• 任何从 TSTZ 到 java 日期、时间、时间戳的映射（例如，通过 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（只有日期、时间和时间戳），因此我们再次丢失了 TZ 信息。

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.

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

2. 使用 Oracle DB“toString”转换：“SELECT TO_CHAR(tstz...) EVENT_TIME ...”。这工作正常，但有两个主要缺点：

   • 现在每个 SELECT 都必须包含 TO_CHAR 调用，这是一个令人头疼的记忆和编写
   • 现在每个 SELECT 都必须添加 EVENT_TIME 列“别名”（需要例如将结果自动序列化为 Json）
     。

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

4. 我希望有第四个很好的选择，但是从整个网络上看，所以 - 我什么也看不到。

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 #1is the shortest and the most readable way (and maintains decent performance, without losing sub-milliseconds or SQL time-based query capabilities).

下面已经给出了很多想法，但似乎没有正确的方法来做到这一点。就我个人而言，我认为使用方法 #1是最短和最易读的方式（并保持不错的性能，而不会丢失亚毫秒或 SQL 基于时间的查询功能）。

This is what I eventually decided to use:

这是我最终决定使用的：

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

Thanks for good answers everyone,
B.

感谢大家的好回答，
B。

采纳答案by Borka

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:

由于看起来没有什么神奇的方法可以正确执行此操作，因此最简单和最短的方法是#1。具体来说，这是所有需要的代码：

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

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

B.

B.

回答by Devon_C_Miller

A slight improvement to #2:

对#2 的轻微改进：

CREATE OR REPLACE PACKAGE FORMAT AS
  FUNCTION TZ(T TIMESTAMP WITH TIME ZONE) RETURN VARCHAR2;
END;
/
CREATE OR REPLACE PACKAGE BODY FORMAT AS
  FUNCTION TZ(T TIMESTAMP WITH TIME ZONE) RETURN VARCHAR2
  AS
  BEGIN
    RETURN TO_CHAR(T,'YYYYMMDD"T"HH24:MI:SS.FFTZHTZM');
  END;
END;
/

The in SQL this becomes:

在 SQL 这变成：

SELECT FORMAT.TZ(tstz) EVENT_TIME ...

It's more readable.
If you ever need to change it, it's 1 place.
The downside is it is an extra function call.

它更具可读性。
如果您需要更改它，则为 1 个位置。
缺点是它是一个额外的函数调用。

回答by Philippe Marschall

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.

JDBC getObject() 或 getTIMESTAMPTZ() 都返回 Oracle 的 TIMESTAMPTZ 对象，这实际上是无用的，因为它没有任何转换为​​ Calendar（只有日期、时间和时间戳），因此我们再次丢失了 TZ 信息。

That would be my recommendation as the only reliable way to get the information you seek.

这将是我的建议，因为这是获取您所寻求信息的唯一可靠方法。

If you are on Java SE 8 and have ojdbc8 then you can use getObject(int, OffsetDateTime.class). Be aware that when you use getObject(int, ZonedDateTime.class) you may be affected by bug 25792016.

如果您使用的是 Java SE 8 并且有 ojdbc8，那么您可以使用getObject(int, OffsetDateTime.class）。请注意，当您使用getObject(int, ZonedDateTime.class) 时，您可能会受到错误 25792016 的影响。

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.

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

This is what we ultimately went withuntil bug free JSR-310 support is available in the Oracle JDBC driver. We determined this was the only reliable way to get the information we want.

这就是我们最终采用的方法，直到 Oracle JDBC 驱动程序中提供无错误 JSR-310 支持。我们确定这是获取我们想要的信息的唯一可靠方式。

回答by AlexWien

you need two values: time utc in millis since 1970 and timezone offset fom utc.
So store them as a pair and forward them as a pair.

您需要两个值：自 1970 年以来以毫秒为单位的时间 utc 和时区偏移量 utc。
因此，将它们存储为一对并将它们作为一对转发。

class DateWithTimeZone {
long timestampUtcMillis;
// offset in seconds
int tzOffsetUtcSec;
}

A Date is a pair of numbers. It is not a String. So a machine interface should not contain a date represented by a iso string, although that is handy to debug. If even java cannot parse that iso date, how do you think that your clients can do?

日期是一对数字。它不是字符串。因此，机器接口不应包含由 iso 字符串表示的日期，尽管这便于调试。如果连 java 都不能解析那个 iso 日期，你认为你的客户能做什么？

If you design an interface to your clients, think how they can parse that. And in advance write a code that shows that.

如果您为客户设计了一个界面，请考虑他们如何解析它。并提前编写一个代码来显示这一点。

回答by GinoA

This is untested, but seems like it ought to be a workable approach. I'm not sure about parsing the TZ name out, but just treating the two parts of the TZTZ object as separate inputs to Calendar seems like the was to go.

这是未经测试的，但似乎应该是一种可行的方法。我不确定是否解析出 TZ 名称，但将 TZTZ 对象的两个部分视为 Calendar 的单独输入似乎是可行的。

I'm not sure whether longValue() will return the value in local or GMT/UCT. If it's not GMT, you should be able to load a calendar as UTC and ask it for a Calendar converted to local TZ.

我不确定 longValue() 是否会返回本地或 GMT/UCT 的值。如果不是 GMT，您应该能够将日历加载为 UTC 并要求它转换为本地 TZ 的日历。

public Calendar toCalendar(oracle.sql.TIMESTAMPTZ myOracleTime) throws SQLException {
    byte[] bytes = myOracleTime.getBytes();
    String tzId = "GMT" + ArrayUtils.subarray(bytes, ArrayUtils.lastIndexOf(bytes, (byte) ' '), bytes.length);
    TimeZone tz = TimeZone.getTimeZone(tzId);
    Calendar cal = Calendar.getInstance(tz);
    cal.setTimeInMillis(myOracleTime.longValue());
    return cal;
}

回答by Keith

Do you really care about sub-millisecond precision? If not converting from a UTC millisecond + timezone-offset to your required string is a one-liner using joda-time:

你真的关心亚毫秒级的精度吗？如果不从 UTC 毫秒 + 时区偏移量转换为您所需的字符串，则是使用 joda-time 的单行：

    int offsetMillis = rs.getInt(1);
    Date date = rs.getTimestamp(2);

    String iso8601String =
            ISODateTimeFormat
                    .dateTime()
                    .withZone(DateTimeZone.forOffsetMillis(offsetMillis))
                    .print(date.getTime());

Prints, for example (current time in +9:00):

打印，例如（当前时间在 +9:00）：

2013-07-18T13:05:36.551+09:00

Regarding the database: Two columns, one for the offset, one for the date. The date column could be an actual date type (thus making many, timezone-independent anyway, db date functions available). For time-zone dependent queries (such as the mentioned global hourly histogram) perhaps a view could expose columns: local_hour_of_day, local_minute_of_hour, etc.

关于数据库：两列，一列用于偏移量，一列用于日期。日期列可以是实际的日期类型（因此可以使用许多与时区无关的 db 日期函数）。对于时区相关查询（例如提到的全局每小时直方图），视图可能会公开列：local_hour_of_day、local_minute_of_hour 等。

This is likely how one would have to do it if no TSTZ datatype was available--which, considering Oralce's poor support, is the nearly the case for practical purposes. Who wants to use an Oracle specific features anyway! :-)

如果没有可用的 TSTZ 数据类型，这可能是人们不得不这样做的方式——考虑到 Oralce 的支持不佳，实际用途几乎就是这种情况。谁想使用 Oracle 的特定功能呢！:-)

回答by SEIYA

The solution with oracle is SELECT SYSTIMESTAMP FROM DUAL

oracle的解决方案是SELECT SYSTIMESTAMP FROM DUAL