Tipos de Datos Fecha

muchas veces me he encontrado con problemas relacionados a la búsqueda de información basada en fechas.

El siguiente tipo de query es muy común:

SQL> select count(1)
2 from ra_customer_trx_all
3 where trunc(creation_date) between
4 to_date('01/04/2008','dd/mm/yyyy')
5 and to_date('30/04/2008','dd/mm/yyyy');

COUNT(1)
----------
206171

Y siempre encuentro el mismo razonamiento de los desarrolladores. "Debido a que la fehca viene guardada con un timestamp, es necesario poner un trunc()".

Y mi respuesta siempre es la siguiente:

Oracle no es que guarde una fecha con o sin formato. Oracle guarda una fecha! Creo que el paradigma de que oracle guarda un formato es erroneo, Oracle solo guarda un tipo de dato fecha, y las sesiones son las que llevan un formato. por esa rozón siempre recomiendo cambiar el tipo de query de arriba por el siguiente:

SQL> select count(1)
2 from ra_customer_trx_all
3 where creation_date >= to_date('01/04/2008','dd/mm/yyyy')
4* and creation_date < to_date('30/04/2008','dd/mm/yyyy') + 1

COUNT(1)
----------
206171

El resultado es el mismo, y en caso de tener un índice creado en el campo creation_date seguramente será usado, de forma contraria al trunc(), ya que al aplicar una función al campo, se evita el uso del índice.

Para poder revisar cómo se guarda un tipo de dato fecha en oracle, hacemos lo siguiente:

SQL> create table prueba (campo1 char(3),campo2 date, campo3 char(3));

Tabla creada.

SQL> insert into prueba values('XXX',to_date('01/01/2000','dd/mm/yyyy'),'YYY');

1 fila creada.

SQL> commit;

Confirmaci¾n terminada.

SQL> SELECT dbms_rowid.rowid_relative_fno(rowid) "Datafile",
  2    dbms_rowid.rowid_block_number(rowid) "Bloque"
  3  FROM prueba;

  Datafile     Bloque
---------- ----------
         1      63314

SQL> alter system dump datafile 1 block 63314;

Sistema modificado.

SQL> oradebug setmypid
Sentencia procesada.
SQL> oradebug tracefile_name;
c:\oracle\product\admin\orcl\udump\orcl_ora_3196.trc
 

Una vez que hacemos el dump del bloque y sabiendo que el heade del bloque crece de arriba hacia abajo y los datos del bloque de abajo hacia arriba, nos vamos al final del bloque

ABCC7D0 19330E1E 066C7807 353B1303 30303213  [..3..xl...;5.200]
ABCC7E0 38302D35 3A30332D 03012C31 58585803  [5-08-30:1,...XXX]
ABCC7F0 01647807 01010101 59595903 68820601  [.xd......YYY...h]
Block header dump:  0x0040f752

Vemos que se repiten 3 veces el 0x58 y 3 veces el 0x59

0x58 = 88
0x89 = 89

Con estos valores vemos lo siguiente

SQL> select chr(88) from dual;

C
-
X

SQL> select chr(89) from dual;

C
-
Y

De esta forma sabemos que en el registro que insertamos, la fecha se guarda entre 58585803 y 59595903 y la parte que nos interesa correspondiente a la fecha es la siguiente

01647807 01010101

0x01 = 1 = Mes 1
0x64 = 100 = Año 00
0x78 = 120 = Siglo 20
0x07 = 7 = Longitud de 7 bytes
0x01 = 1 = Hora 00
0x01 = 1 = Minuto 00
0x01 = 1 = Segundo 00
0x01 = 1 = Día 1

De esta forma sabemos que Oracle, se use o no un formato, siempre guarda un tipo de datos fecha y no un formato. y que Oracle los datos que guardan son:

Siglo

Año

Mes

Dia

Hora

Minuto

Segundo

y todo esto ya sea insertando una fecha 'dd/mm/yyyy hh24:mi:ss' o simplemente 'dd/mm/rr'.