Después de de nuestra larga compilación de información dimos con la solución esta interrogante que pueden tener algunos lectores. Te ofrecemos la solución y nuestro deseo es que te resulte de gran apoyo.
Solución:
Ok, normalmente no respondo mis propias preguntas, pero después de un poco de retoques, descubrí definitivamente cómo Oracle almacena el resultado de una resta de FECHA.
Cuando resta 2 fechas, el valor no es un tipo de datos NÚMERO (como el manual de referencia SQL de Oracle 11.2 le haría creer). El número de tipo de datos interno de una resta de FECHA es 14, que es un tipo de datos interno no documentado (NÚMERO es el tipo de datos interno número 2). Sin embargo, en realidad se almacena como 2 números separados con signo en complemento a dos, con los primeros 4 bytes usados para representar el número de días y los últimos 4 bytes usados para representar el número de segundos.
Un ejemplo de una resta de FECHA 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 2 números de 4 bytes con signo en complemento a dos separados. Dado que no hay decimales en este caso (364 días y 0 horas exactamente), los últimos 4 bytes son todos 0 y se pueden ignorar. Para los primeros 4 bytes, debido a que mi CPU tiene una arquitectura little-endian, los bytes están invertidos y deben leerse como 1108 o 0x16c, que es decimal 364.
Un ejemplo de una resta de FECHA 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, dado que esto está en la codificación de números binarios con signo de complemento a dos, sabemos que el número es negativo porque el dígito binario más a la izquierda es un 1. Para convertir esto en un número decimal, tendríamos que invertir el complemento a 2 (restar 1 y luego hacer el complemento a uno) dando como resultado: 00000000 00000101 10011110 00100000 que es igual a -368160 como se sospecha.
Un ejemplo de una resta de FECHA 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 de 0 días y 6 horas, se espera que los primeros 4 bytes sean 0. Para los últimos 4 bytes, podemos invertirlos (porque la CPU es little-endian) y obtener 84,96 = 01010100 01100000 base 2 = 21600 en decimal. Convertir 21600 segundos a horas te da 6 horas, que es la diferencia que esperábamos.
Espero que esto ayude a cualquiera que se pregunte cómo se almacena realmente una resta de FECHA.
Obtiene el error de sintaxis porque la matemática de la fecha 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 en su ejemplo en un INTERVALO usando 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>
Según la conversión inversa con la función NUMTODSINTERVAL(), parece que se pierde algo de redondeo en la traducción.
Sección de Reseñas y Valoraciones
Si te gustó nuestro trabajo, tienes la libertad de dejar un post acerca de qué le añadirías a este escrito.