Oracle / JDBC: recuperando el valor de TIMESTAMP WITH TIME ZONE en formato ISO 8601

Se ha dicho mucho (y escrito en SO) sobre partes del tema, pero no de una manera completa y completa, de modo que podamos tener una solución “definitiva, que cubra todo” para que todos la puedan usar.

Tengo una base de datos Oracle en la que almaceno la fecha y la hora y la zona horaria de los eventos globales, por lo que la TZ original debe conservarse y entregarse al lado del cliente cuando se solicite. Idealmente, podría funcionar bien utilizando el formato estándar ISO 8601 “T” que se puede almacenar en Oracle usando el tipo de columna “TIMESTAMP WITH TIME ZONE” (“TSTZ”).

Algo así como ‘2013-01-02T03: 04: 05.060708 + 09: 00’

Todo lo que necesito hacer es recuperar el valor anterior de la base de datos y enviarlo al cliente sin ninguna manipulación.

El problema es que Java carece de compatibilidad con ISO 8601 (o cualquier otro tipo de datos date + time + nano + tz) y la situación es aún peor, porque el controlador Oracle JDBC (ojdbc6.jar) tiene incluso menos compatibilidad con TSTZ (a diferencia de Oracle DB en sí es bien soportado).

Específicamente, esto es lo que no debo o no puedo hacer:

  • Cualquier asignación de TSTZ a fecha, hora, fecha y hora java (por ejemplo, a través de las llamadas getTimestamp () de JDBC) no funcionará porque pierdo TZ.
  • El controlador JDBC de Oracle no proporciona ningún método para asignar TSTZ al objeto Calendario de Java (esto podría ser una solución, pero no existe)
  • JDBC getString () podría funcionar, pero el controlador JDBC de Oracle devuelve una cadena en formato
    ‘2013-01-02 03: 04: 05.060708 +9: 00’, que no cumple con la norma ISO 8601 (sin “T”, sin 0 al final en TZ, etc.). Además, este formato está codificado (!) Dentro de la implementación del controlador JDBC de Oracle, que también ignora la configuración regional de JVM y la configuración de formato de sesión de Oracle (es decir, ignora la variable de sesión NLS_TIMESTAMP_TZ_FORMAT).
  • JDBC getObject (), u getTIMESTAMPTZ (), ambos devuelven el objeto TIMESTAMPTZ de Oracle, que es prácticamente inútil, porque no tiene ninguna conversión al calendario (solo fecha, hora y fecha y hora), por lo que nuevamente perdemos la información de TZ.

Entonces, aquí están las opciones que me quedan:

  1. Use JDBC getString (), y manipule la cadena para corregir y hacer que cumpla con la norma ISO 8601. Esto es fácil de hacer, pero existe el peligro de morir si Oracle cambia el formato interno getString () codificado. Además, al mirar el código fuente de getString (), parece que usar getString () también resultaría en una penalización de rendimiento.

  2. Utilice la conversión “toString” de la base de datos Oracle: “SELECT TO_CHAR (tstz …) EVENT_TIME …”. Esto funciona bien, pero tiene 2 desventajas importantes:

    • Cada SELECT ahora tiene que incluir TO_CHAR llamada, que es un dolor de cabeza para recordar y escribir
    • Cada SELECT ahora tiene que agregar la columna EVENT_TIME “alias” (necesario, por ejemplo, para serializar el resultado a Json automáticamente)
      .
  3. Use la clase Java de TIMESTAMPTZ de Oracle y extraiga el valor relevante manualmente de su estructura de matriz de bytes interna (documentada) (es decir, implemente mi propio método toString () que Oracle olvidó implementar allí). Esto es riesgoso si Oracle cambia la estructura interna (es poco probable) y exige una función relativamente complicada de implementar y mantener.

  4. Espero que haya 4ª, una gran opción, pero si miro por toda la web y SO, no puedo ver ninguna.

Ideas? Opiniones?

ACTUALIZAR

Se han dado muchas ideas a continuación, pero parece que no hay una forma adecuada de hacerlo. Personalmente, creo que usar el método # 1 es la forma más corta y más legible (y mantiene un rendimiento decente, sin perder submilisegundos o capacidades de consulta basadas en el tiempo de SQL ).

Esto es lo que finalmente decidí usar:

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

Gracias por las buenas respuestas a todos,
SEGUNDO.

Una ligera mejora a # 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; / 

El en SQL esto se convierte en:

 SELECT FORMAT.TZ(tstz) EVENT_TIME ... 

Es más legible.
Si alguna vez necesitas cambiarlo, es 1 lugar.
El inconveniente es que es una llamada de función adicional.

JDBC getObject (), u getTIMESTAMPTZ (), ambos devuelven el objeto TIMESTAMPTZ de Oracle, que es prácticamente inútil, porque no tiene ninguna conversión al calendario (solo fecha, hora y fecha y hora), por lo que nuevamente perdemos la información de TZ.

Esa sería mi recomendación como la única forma confiable de obtener la información que busca.

Si está en Java SE 8 y tiene ojdbc8, entonces puede usar getObject(int, OffsetDateTime.class ). Tenga en cuenta que cuando usa getObject(int, ZonedDateTime.class ) puede verse afectado por el error 25792016 .

Use la clase Java de TIMESTAMPTZ de Oracle y extraiga el valor relevante manualmente de su estructura de matriz de bytes interna (documentada) (es decir, implemente mi propio método toString () que Oracle olvidó implementar allí). Esto es riesgoso si Oracle cambia la estructura interna (es poco probable) y exige una función relativamente complicada de implementar y mantener.

Esto es lo que finalmente utilizamos hasta que el soporte JSR-310 sin errores esté disponible en el controlador JDBC de Oracle. Determinamos que esta era la única forma confiable de obtener la información que deseamos.

necesita dos valores: tiempo utc en milis desde 1970 y timezone offset fom utc.
Así que guárdalos como un par y envíalos como un par.

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

Una fecha es un par de números. No es una cadena. Por lo tanto, una interfaz de máquina no debe contener una fecha representada por una cadena iso, aunque eso es útil para la depuración. Si incluso java no puede analizar esa fecha, ¿cómo crees que pueden hacer tus clientes?

Si diseñas una interfaz para tus clientes, piensa en cómo pueden analizarla. Y de antemano escribe un código que muestre eso.

Esto no se ha probado, pero parece que debería ser un enfoque viable. No estoy seguro de analizar el nombre TZ, pero parece que las dos partes del objeto TZTZ son entradas separadas al Calendario.

No estoy seguro de si longValue () devolverá el valor en local o GMT / UCT. Si no es GMT, deberías poder cargar un calendario como UTC y pedirle un calendario convertido a TZ local.

 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; } 

¿De verdad te importa la precisión de menos de milisegundos? Si no se está convirtiendo de un milisegundo UTC + desplazamiento de zona horaria a su cadena requerida, es una línea usando joda-time:

  int offsetMillis = rs.getInt(1); Date date = rs.getTimestamp(2); String iso8601String = ISODateTimeFormat .dateTime() .withZone(DateTimeZone.forOffsetMillis(offsetMillis)) .print(date.getTime()); 

Imprime, por ejemplo (hora actual en +9: 00):

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

Con respecto a la base de datos: Dos columnas, una para el desplazamiento, una para la fecha. La columna de fecha podría ser un tipo de fecha real (por lo tanto, hace que muchas funciones de fecha de db estén disponibles de forma independiente en la zona horaria). Para consultas dependientes de la zona horaria (como el histogtwig horario global mencionado) tal vez una vista podría exponer columnas: local_hour_of_day, local_minute_of_hour, etc.

Esto es probablemente como uno tendría que hacerlo si no hubiera un tipo de datos TSTZ disponible, lo cual, considerando el poco apoyo de Oralce, es casi el caso para propósitos prácticos. ¡Quién quiere usar las características específicas de Oracle de todos modos! 🙂

Como parece que no hay una forma mágica de hacer esto bien, el método más simple y más corto sería el # 1. Específicamente, este es todo el código necesario:

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

parece que solo agregar ‘T’ es suficiente, aunque un perfeccionista probablemente pondría más cosméticos (regex puede optimizarse, por supuesto), por ejemplo: rs.getString (col) .replaceFirst (“”, “T”). replaceAll (” “,” “) .replaceFirst (” \ + ([0-9]) \: “,” + 0 $ 1: “);

SEGUNDO.

La solución con oracle es SELECT SYSTIMESTAMP FROM DUAL.