plan - Declaración de consulta SQL de Oracle y condiciones con marcas de tiempo y fechas ISO

optimizar inner join sql server

Question

Necesito una consulta que seleccionará los registros de la base de datos Oracle en función de la condición de fecha y hora. Ejemplo a continuación:

SELECT * FROM table_name WHERE [modification_date] >= ''2014-01-28T12:00:00Z'';

Como datetime utilicé la fecha ISO y esto es "Debe ser". En la base de datos Oracle XE, una columna [fecha_modificación] tiene el tipo "Indicación de fecha y hora con zona horaria". Y ahora es mi pregunta: ¿cómo convertir la fecha ISO en la consulta a una búsqueda adecuada en la base de datos?

Intenté agregar to_timestamp_tz a la declaración de consulta.

SELECT * FROM table_name
WHERE MODIFICATION_DATE >= to_timestamp_tz(''2014-01-28T00:00:0Z'', ''YYYY-MM-DD"T"HH24:MI:SS'');

Pero consigue este error:

Error SQL [1830] [22008]: ORA-01830: la imagen del formato de fecha finaliza antes de convertir toda la cadena de entrada

Answer 1

Mira la función to_timestamp_tz .

Suponiendo que Z es para Zulu / UTC, es probable que haga un reemplazo de Z con +00: 00 y trabaje con la función to_timestamp_tz

SELECT * FROM table_name 
WHERE modification_date >= to_timestamp_tz(replace(''2014-01-28T12:00:00z'',''z'',''+00:00''),''YYYY-MM-DD"T"hh24:mi:sstzh:tzm'')

Answer 2

Basado en una pregunta anterior , es tentador tratar tanto a la T como a la Z como literales de carácter, y básicamente ignorarlos, usando:

to_timestamp_tz(''2014-01-28T12:00:0Z'', ''YYYY-MM-DD"T"HH24:MI:SS"Z"'')

Si usa to_timestamp_tz() sin especificar una zona horaria, se establece de manera predeterminada en la zona horaria de la sesión, como lo haría con to_timestamp() ; por lo que un tiempo especificado en Zulu / UTC pierde esa información de zona:

alter session set time_zone = ''America/New_York'';
select to_timestamp_tz(''2014-01-28T12:00:0Z'',
  ''YYYY-MM-DD"T"HH24:MI:SS"Z"'') from dual;

TO_TIMESTAMP_TZ(''2014-01-28T12:00:0Z'',''YYYY-MM-DD"T"HH24:MI:SS"Z"'')
-------------------------------------------------------------------
28-JAN-14 12.00.00.000000000 AMERICA/NEW_YORK                       

Su hora 12:00 se muestra como 12:00 en Nueva York, no a las 12:00 UTC.

Una conversión más segura, suponiendo que siempre se supone que sus valores representan el UTC, es especificar el huso horario explícitamente con la función from_tz() :

WHERE MODIFICATION_DATE >= from_tz(to_timestamp(''2014-01-28T12:00:0Z'',
  ''YYYY-MM-DD"T"HH24:MI:SS"Z"''), ''UTC'')

Esto obtiene la hora UTC correctamente:

alter session set time_zone = ''America/New_York'';
select from_tz(to_timestamp(''2014-01-28T12:00:0Z'',
  ''YYYY-MM-DD"T"HH24:MI:SS"Z"''), ''UTC'') from dual;

FROM_TZ(TO_TIMESTAMP(''2014-01-28T12:00:0Z'',''YYYY-MM-DD"T"HH24:MI:SS"Z"''),''UTC'')
-------------------------------------------------------------------------------
28-JAN-14 12.00.00.000000000 UTC