sql - to_date - Restar fechas en Oracle-Número o tipo de datos de intervalo?
to_date oracle ejemplos (4)
Obtiene el error de sintaxis porque la fecha matemática no devuelve un NÚMERO, pero devuelve un INTERVALO:
SQL> SELECT DUMP(SYSDATE - start_date) from test;
DUMP(SYSDATE-START_DATE)
--------------------------------------
Typ=14 Len=8: 188,10,0,0,223,65,1,0
Primero debe convertir el número de su ejemplo en un INTERVAL utilizando la función NUMTODSINTERVAL
Por ejemplo:
SQL> SELECT (SYSDATE - start_date) DAY(5) TO SECOND from test;
(SYSDATE-START_DATE)DAY(5)TOSECOND
----------------------------------
+02748 22:50:04.000000
SQL> SELECT (SYSDATE - start_date) from test;
(SYSDATE-START_DATE)
--------------------
2748.9515
SQL> select NUMTODSINTERVAL(2748.9515, ''day'') from dual;
NUMTODSINTERVAL(2748.9515,''DAY'')
--------------------------------
+000002748 22:50:09.600000000
SQL>
Basado en el molde inverso con la función NUMTODSINTERVAL (), parece que se pierde algo de redondeo en la traducción.
Tengo una pregunta sobre el funcionamiento interno de los tipos de datos Oracle DATE e INTERVAL. De acuerdo con Oracle 11.2 SQL Reference , cuando se restan 2 tipos de datos DATE, el resultado será un tipo de datos NUMBER.
En las pruebas superficiales, esto parece ser cierto:
CREATE TABLE test (start_date DATE);
INSERT INTO test (start_date) VALUES (date''2004-08-08'');
SELECT (SYSDATE - start_date) from test;
devolverá un tipo de datos NUMBER.
Pero ahora si lo haces:
SELECT (SYSDATE - start_date) DAY(5) TO SECOND from test;
obtienes un tipo de datos INTERVALO. En otras palabras, Oracle puede convertir el NÚMERO de la resta DATE en un tipo INTERVALO.
Así que ahora pensé que podría tratar de poner un tipo de datos NUMBER directamente entre corchetes (en lugar de hacer ''SYSDATE - start_date'' que da como resultado un NUMBER de todos modos):
SELECT (1242.12423) DAY(5) TO SECOND from test;
Pero esto da como resultado el error:
ORA-30083: syntax error was found in interval value expression
Entonces mi pregunta es: ¿qué está pasando aquí? Parece que restar fechas debería llevar a un NÚMERO (como se demuestra en la instrucción SELECT # 1), que NO PUEDE ser lanzado automáticamente al tipo de INTERVALO (como se demuestra en la instrucción SELECT # 3). Pero Oracle parece ser capaz de hacer eso de alguna manera si usa la expresión de resta DATE en lugar de ingresar un NUMBER en bruto (instrucción SELECT # 2).
Gracias
Ok, normalmente no respondo mis propias preguntas, pero después de un poco de retoques, he descubierto definitivamente cómo Oracle almacena el resultado de una resta DATE.
Cuando resta 2 fechas, el valor no es un tipo de datos NUMBER (como lo haría creer el manual de Oracle 11.2 SQL Reference ). El número de tipo de datos interno de una resta DATE es 14, que es un tipo de datos interno no documentado (NUMBER es el tipo de datos interno número 2 ). Sin embargo, en realidad se almacena como 2 números de complemento del complemento de dos separados, con los primeros 4 bytes utilizados para representar el número de días y los últimos 4 bytes utilizados para representar el número de segundos.
Un ejemplo de una resta DATE que da como resultado una diferencia entera positiva:
select date ''2009-08-07'' - date ''2008-08-08'' from dual;
Resultados en:
DATE''2009-08-07''-DATE''2008-08-08''
---------------------------------
364
select dump(date ''2009-08-07'' - date ''2008-08-08'') from dual;
DUMP(DATE''2009-08-07''-DATE''2008
-------------------------------
Typ=14 Len=8: 108,1,0,0,0,0,0,0
Recuerde que el resultado se representa como un complemento de dos separados con 4 números de 4 bytes. Como no hay decimales en este caso (364 días y 0 horas exactamente), los últimos 4 bytes son todos ceros y se pueden ignorar. Para los primeros 4 bytes, debido a que mi CPU tiene una arquitectura little-endian, los bytes se invierten y deben leerse como 1,108 o 0x16c, que es el decimal 364.
Un ejemplo de una resta DATE que da como resultado una diferencia entera negativa:
select date ''1000-08-07'' - date ''2008-08-08'' from dual;
Resultados en:
DATE''1000-08-07''-DATE''2008-08-08''
---------------------------------
-368160
select dump(date ''1000-08-07'' - date ''2008-08-08'') from dual;
DUMP(DATE''1000-08-07''-DATE''2008-08-0
------------------------------------
Typ=14 Len=8: 224,97,250,255,0,0,0,0
Nuevamente, dado que estoy usando una máquina little-endian, los bytes están invertidos y deben leerse como 255,250,97,224 que corresponde a 11111111 11111010 01100001 11011111. Ahora que se trata de una codificación numérica binaria con signo de complemento a dos, sabemos que el número es negativo porque el dígito binario más a la izquierda es 1. Para convertir esto en un número decimal, tendríamos que revertir el complemento a 2 (restar 1 luego hacer el complemento) resultando en: 00000000 00000101 10011110 00100000 que es igual a -368160 como se sospecha.
Un ejemplo de una resta DATE que da como resultado una diferencia decimal:
select to_date(''08/AUG/2004 14:00:00'', ''DD/MON/YYYY HH24:MI:SS''
- to_date(''08/AUG/2004 8:00:00'', ''DD/MON/YYYY HH24:MI:SS'') from dual;
TO_DATE(''08/AUG/200414:00:00'',''DD/MON/YYYYHH24:MI:SS'')-TO_DATE(''08/AUG/20048:00:
--------------------------------------------------------------------------------
.25
La diferencia entre esas 2 fechas es de 0,25 días o 6 horas.
select dump(to_date(''08/AUG/2004 14:00:00'', ''DD/MON/YYYY HH24:MI:SS'')
- to_date(''08/AUG/2004 8:00:00'', ''DD/MON/YYYY HH24:MI:SS'')) from dual;
DUMP(TO_DATE(''08/AUG/200414:00:
-------------------------------
Typ=14 Len=8: 0,0,0,0,96,84,0,0
Ahora esta vez, dado que la diferencia es 0 días y 6 horas, se espera que los primeros 4 bytes sean 0. Para los últimos 4 bytes, podemos revertirlos (porque la CPU es little-endian) y obtener 84,96 = 01010100 01100000 base 2 = 21600 en decimal. La conversión de 21600 segundos a horas te da 6 horas, que es la diferencia que esperábamos.
Espero que esto ayude a cualquiera que se esté preguntando cómo se almacena realmente una resta DATE.
Algunos puntos:
Restando una fecha de otro resulta en un número; restar una marca de tiempo de otro resultados en un intervalo.
Oracle convierte las marcas de tiempo en fechas internamente al realizar la aritmética de la marca de tiempo.
Las constantes de intervalo no se pueden usar en aritmética de fecha o de indicación de fecha y hora.
seleccione TIMEDIFF (STR_TO_DATE (''07:15 PM '', ''% h:% i% p''), STR_TO_DATE ('' 9:58 AM '', ''% h:% i% p''))