to_date restar que mayor insertar horas fechas fecha entre ejemplos diferencia dias con comparar oracle timezone timestamp dst

que - restar horas timestamp oracle



Comparación de fechas de Oracle roto por DST (2)

Hemos estado depurando un problema con una consulta SQL ejecutada desde un servidor de aplicaciones que ejecutaba Java a través de Hibernate. El error:

[3/10/14 10:52:07:143 EDT] 0000a984 JDBCException W org.hibernate.util.JDBCExceptionReporter logExceptions SQL Error: 1878, SQLState: 22008 [3/10/14 10:52:07:144 EDT] 0000a984 JDBCException E org.hibernate.util.JDBCExceptionReporter logExceptions ORA-01878: specified field not found in datetime or interval

Hemos podido reducir esto al simple SQL a continuación.

select * from MY_TABLE T where T.MY_TIMESTAMP >= (CURRENT_TIMESTAMP - interval ''1'' hour );

Cuando ejecutamos esto en la misma base de datos, obtenemos el error:

ORA-01878: specified field not found in datetime or interval 01878. 00000 - "specified field not found in datetime or interval" *Cause: The specified field was not found in the datetime or interval. *Action: Make sure that the specified field is in the datetime or interval.

La columna MY_TIMESTAMP se define como TIMESTAMP(6) .

FWIW, si cambiamos la comparación en el SQL anterior de >= a <= , la consulta funciona.

Suponemos que esto tiene algo que ver con el cambio de hora (estamos en América / New_York) pero estamos teniendo problemas para tratar de averiguar a dónde ir desde aquí con nuestra depuración.

Además, hemos visto este problema con una consulta similar que se ejecuta a través de MyBatis y el error se ve así:

### Error querying database. Cause: java.sql.SQLException: ORA-01878: specified field not found in datetime or interval ### The error may involve defaultParameterMap ### The error occurred while setting parameters ### Cause: java.sql.SQLException: ORA-01878: specified field not found in datetime or interval

ACTUALIZACIÓN: Un compañero de equipo en Windows cambió su configuración de Fecha y hora de Windows al desmarcar "Ajustar automáticamente el reloj para el horario de verano" y luego abrió una nueva instancia de SQLDeveloper. La segunda instancia puede ejecutar la consulta sin ningún problema, pero la primera (con la configuración DST anterior) aún falla.

Gracias a Kordirko por la escritura extremadamente detallada. Creo que en el futuro, buscaremos diferentes formas de comparar fechas que no son tan propensas a errores. Mientras tanto, pudimos descubrir el problema y una solución temporal y a largo plazo.

Primero, más detalles sobre el tema. Resulta que los valores almacenados en el campo TIMESTAMP en la base de datos eran incorrectos. Vimos esto usando la función de volcado y examinando los bytes. Si miras el quinto byte en el resultado a continuación, verás la hora (esta es en realidad la hora + 1, por lo que 5 es en realidad 4AM). Para los valores entre 3AM y 4AM, observará que el 5to byte es 3, que representa 2AM. 2 AM 9 de marzo de 2014 en EST no existe: este es un momento incorrecto de acuerdo con las reglas de DST y las reglas de Oracle .

09-MAR-14 03.06.21.522000000 AM Typ=180 Len=11: 120,114,3,9,3,7,22,31,29,22,128 09-MAR-14 03.32.37.869000000 AM Typ=180 Len=11: 120,114,3,9,3,33,38,51,203,227,64 09-MAR-14 03.36.49.804000000 AM Typ=180 Len=11: 120,114,3,9,3,37,50,47,236,17,0 09-MAR-14 03.43.47.328000000 AM Typ=180 Len=11: 120,114,3,9,3,44,48,19,140,226,0 09-MAR-14 03.47.55.255000000 AM Typ=180 Len=11: 120,114,3,9,3,48,56,15,50,253,192 09-MAR-14 03.55.45.129000000 AM Typ=180 Len=11: 120,114,3,9,3,56,46,7,176,98,64 09-MAR-14 04.05.03.325000000 AM Typ=180 Len=11: 120,114,3,9,5,6,4,19,95,27,64 09-MAR-14 04.28.41.267000000 AM Typ=180 Len=11: 120,114,3,9,5,29,42,15,234,24,192 09-MAR-14 04.35.16.072000000 AM Typ=180 Len=11: 120,114,3,9,5,36,17,4,74,162,0 09-MAR-14 04.41.07.260000000 AM Typ=180 Len=11: 120,114,3,9,5,42,8,15,127,73,0 09-MAR-14 04.46.31.047000000 AM Typ=180 Len=11: 120,114,3,9,5,47,32,2,205,41,192 09-MAR-14 04.53.33.471000000 AM Typ=180 Len=11: 120,114,3,9,5,54,34,28,18,227,192

Después de mucha investigación y discusión, nos centramos en el hecho de que nuestra versión del controlador Oracle JDBC (11.2.0.2) podría haber estado insertando los valores incorrectos. La página de información de Oracle en 11.2.0.3 hace referencia a un error que parece ser nuestro problema: "9785135 conversión DST no es correcta usando jdbc 11g timestamtz". Escribimos una clase de prueba rápida que inserta valores desde el 9 de marzo de 2014, 1:50 a.m. a 4:00 a.m., utilizando los controladores 11.2.0.2 y 11.2.0.3. Aquí hay un fragmento de lo que se insertó en el archivo db:

DRIVER_V JAVA_DATE_AS_STRING ORACLE_TIMESTAMP DUMP(ORACLE_TIMESTAMP) 11.2.0.2.0/11/2 Sun Mar 09 01:50:00 EST 2014 09-MAR-14 01.50.00.000000000 AM Typ=180 Len=7: 120,114,3,9,2,51,1 11.2.0.2.0/11/2 Sun Mar 09 03:00:00 EDT 2014 09-MAR-14 03.00.00.000000000 AM Typ=180 Len=7: 120,114,3,9,3,1,1 --Invalid timestamp 11.2.0.3.0/11/2 Sun Mar 09 01:50:00 EST 2014 09-MAR-14 01.50.00.000000000 AM Typ=180 Len=7: 120,114,3,9,2,51,1 11.2.0.3.0/11/2 Sun Mar 09 03:00:00 EDT 2014 09-MAR-14 03.00.00.000000000 AM Typ=180 Len=7: 120,114,3,9,4,1,1 --Correct timestamp

Notarás que el quinto byte de la marca de tiempo en la segunda fila para las 3:00 a.m. es incorrecto (3). Esto fue insertado usando la versión 11.2.0.2. El mismo valor insertado con la versión 11.2.0.3 se puede encontrar en la cuarta línea con el quinto byte correcto (4).

La solución a largo plazo aquí es actualizar nuestro controlador JDBC. El arreglo a corto plazo aquí fue encontrar las filas que tienen los valores incorrectos y ejecutar una declaración de actualización sobre ellos desde SQL Plus para establecer la hora nuevamente (usando el mismo valor, pero SQL Plus los convertirá correctamente).

Para evitar este error, considere usar un molde explícito de la expresión en la cláusula where para un tipo de marca de tiempo (marca de tiempo sin zona horaria), de esta manera:

select * from MY_TABLE T where T.MY_TIMESTAMP >= cast(CURRENT_TIMESTAMP - interval ''1'' hour As timestamp );

Alternativamente, puede establecer explícitamente la zona horaria de la sesión, por ejemplo, ''-05: 00'', para el horario estándar de Nueva York (invierno),
utilizando ALTER SESSION time_zone = ''-05:00'' , o estableciendo la variable de entorno ORA_SDTZ en todos los entornos del cliente,
vea este enlace para más detalles: http://docs.oracle.com/cd/E11882_01/server.112/e10729/ch4datetime.htm#NLSPG263

Pero también depende de lo que realmente está almacenado en la columna de marca de tiempo en la tabla, por ejemplo, qué representa una marca de tiempo 2014-07-01 15:00:00 , ¿es un "horario de invierno" o un "horario de verano"?

CURRENT_TIMESTAMP función CURRENT_TIMESTAMP devuelve un valor de tipo de datos TIMESTAMP WITH TIME ZONE
vea este enlace: http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions037.htm

Al comparar marcas de tiempo y fechas, Oracle convierte implícitamente los datos al tipo de datos más preciso utilizando la zona horaria de la sesión.
Vea este enlace -> http://docs.oracle.com/cd/E11882_01/server.112/e10729/ch4datetime.htm#NLSPG251

En nuestro caso particular, Oracle lanza la columna de timestamp a la timestamp with time zone tipo de timestamp with time zone .

Oracle determina una zona horaria de sesión desde el entorno del cliente.
Puede determinar la zona horaria de la sesión actual utilizando esta consulta:

select sessiontimezone from dual;

Por ejemplo, en mi PC (Win 7), cuando la opción "" Ajustar automáticamente el reloj para el horario de verano "está marcada, esta consulta regresa (en SQLDeveloper):

SESSIONTIMEZONE --------------- Europe/Belgrade


Cuando desactivo esta opción en Windows y luego reinicio SQLDeveloper, se obtiene:

SESSIONTIMEZONE --------------- +01:00

La zona horaria de la primera sesión es una zona horaria con un nombre de región, para lo cual Oracle usa las reglas de horario de verano para esta región en los cálculos de fechas:

alter session set time_zone = ''Europe/Belgrade''; select cast( timestamp ''2014-01-29 01:30:00'' as timestamp with time zone ) As x, cast( timestamp ''2014-05-29 01:30:00'' as timestamp with time zone ) As y from dual; session SET altered. X Y ---------------------------- ---------------------------- 2014-01-29 01:30:00 EUROPE/B 2014-05-29 01:30:00 EUROPE/B ELGRADE ELGRADE


La última zona horaria usa un desplazamiento fijo "+01: 00" (siempre el "horario de invierno"), y Oracle no aplica ninguna regla de horario de verano para él, simplemente agrega el desplazamiento fijo.

alter session set time_zone = ''+01:00''; select cast( timestamp ''2014-01-29 01:30:00'' as timestamp with time zone ) As x, cast( timestamp ''2014-05-29 01:30:00'' as timestamp with time zone ) As y from dual; session SET altered. X Y ---------------------------- ---------------------------- 2014-01-29 01:30:00 +01:00 2014-05-29 01:30:00 +01:00

Tenga en cuenta, por curiosidad, que los resultados Y en el anterior representan dos momentos diferentes!
014-05-29 01:30:00 EUROPE/BELGRADE no es lo mismo que: 2014-05-29 01:30:00 +01:00

pero en realidad esto:
014-05-29 01:30:00 EUROPE/BELGRADE es igual a: 2014-05-29 01:30:00 +02:00

Lo anterior es solo para informarle de qué tan simple "desprotección de la casilla" podría afectar sus consultas, y dónde buscar un motivo cuando los usuarios se quejan de que "esta consulta funcionó bien en enero, pero arrojó resultados erróneos en julio".

Y aún sobre el tema de ORA-01878, digamos que mi sesión es EUROPE/Warsaw y mi cuadro contiene esta marca de tiempo (sin zona horaria)

''TIMESTAMP''2014-03-30 2:30:00''

Tenga en cuenta que en mi región el cambio del horario de verano, en 2014 años, ocurre el 30 de marzo a las 2:00 a.m.
Simplemente significa que el 30 de marzo, a las 2:00 de la noche, debo despertar y adelantar el reloj de 2:00 a 3:00;)

alter session set time_zone = ''Europe/Warsaw''; select cast( TIMESTAMP''2014-03-30 2:30:00'' as timestamp with time zone ) As x from dual; SQL Error: ORA-01878: podane pole nie zostało znalezione w dacie-godzinie ani w interwale 01878. 00000 - "specified field not found in datetime or interval" *Cause: The specified field was not found in the datetime or interval. *Action: Make sure that the specified field is in the datetime or interval.

Oracle sabe que esta marca de tiempo no es válida en mi región de acuerdo con las reglas del horario de verano, porque no hay tiempo para las 2:30 el 30 de marzo; a las 2:00 el reloj se mueve a las 3:00 y no hay tiempo 2: 30. Por lo tanto, Oracle arroja el error ORA-01878.

Sin embargo, esta consulta funciona perfectamente bien:

alter session set time_zone = ''+01:00''; select cast( TIMESTAMP''2014-03-30 2:30:00'' as timestamp with time zone ) As x from dual; session SET altered. X ---------------------------- 2014-03-30 02:30:00 +01:00

Y esta es una razón de este error: su tabla contiene marcas de tiempo como 2014-03-09 2:30 o menos (para Nueva York, donde ocurren cambios de horario de verano el 9 de marzo y el 2 de noviembre), y Oracle no sabe cómo para convertirlos de la marca de tiempo (sin TZ) a la marca de tiempo con TZ.

La última pregunta: ¿por qué la consulta con >= no funciona, pero la consulta con <= funciona bien?

Funcionan / no funcionan, porque SQLDeveloper solo devuelve las primeras 50 filas (¿quizás 100? Depende de la configuración). La consulta no lee toda la tabla, se detiene cuando se obtienen las primeras 50 (100) filas.
Cambia la consulta de "trabajo" a, por ejemplo:

select sum( EXTRACT(HOUR from MY_TIMESTAMP) ) from MY_TABLE where MY_TIMESTAMP <= (CURRENT_TIMESTAMP - interval ''1'' hour );

Esto fuerza a la consulta a leer todas las filas en la tabla, y aparecerá el error, estoy 100% seguro.