Tipos de datos

La mayor confusión implica los tipos DATE. La base de datos Oracle es particularmente vulnerable en este aspecto porque tiene solamente un tipo DATE que incluye siempre un componente de tiempo.

Es una práctica muy común usar la función TRUNC para eliminar el componente de tiempo. En realidad, no lo suprime pero en su lugar lo sitúa a medianoche porque la base de datos Oracle no tiene un tipo puro de DATE. Para ignorar el componente de tiempo dentro de una búsqueda, se puede usar la función TRUNC en ambos lados de la comparación. Por ejemplo, para buscar las ventas de ayer:

SELECT ...
  FROM sales
 WHERE TRUNC(sale_date) = TRUNC(sysdate - INTERVAL '1' DAY)

Es una sentencia perfectamente válida y correcta pero no se puede usar correctamente el índice sobre SALE_DATE. Es como lo explicado en la sección “Sin distinción entre mayúscula y minúscula usando UPPER o LOWER”; TRUNC(sale_date) es algo completamente diferente de SALE_DATE. Las funciones son una caja negra para la base de datos.

Existe una solución bastante sencilla para este problema: un índices sobre expresiones.

CREATE INDEX index_name
          ON sales (TRUNC(sale_date))

Pero, se debe siempre usar TRUNC(sale_date) dentro del filtro where. Si se usa de forma irregular a veces con TRUNC y a veces sin TRUNC, ¡entonces se deben tener dos índices!

El problema ocurre también con las bases de datos que tienen un tipo de fechas puras si se busca por un periodo largo como se muestra en la siguiente sentencia MySQL:

SELECT ...
  FROM sales
 WHERE DATE_FORMAT(sale_date, "%Y-%M")
     = DATE_FORMAT(now()    , "%Y-%M")

La sentencia usa un formato que contiene solamente el año y el mes. De nuevo, se trata de una sentencia totalmente correcta que tiene el mismo problema anterior. Sin embargo, la solución antes descrita no se aplica para las versiones de MySQL anteriores a la 5.7, porque este sistema de gestión de bases de datos relacional no soporta los índices basados en funciones anteriores a esa versión.

La alternativa es usar una condición de rango explícita. Esta solución genérica funciona con todas las bases de datos:

SELECT ...
  FROM sales
 WHERE sale_date BETWEEN quarter_begin(?) 
                     AND quarter_end(?)

Si has ido haciendo tus tareas, seguramente reconocerás la muestra desde los ejercicios sobre todos los empleados que tienen 42 años de edad.

Basta con un índice en orden sobre SALE_DATE para optimizar esta sentencia. Las funciones QUARTER_BEGIN y QUARTER_END calculan los límites de las fechas. El cálculo puede terminar siendo un poco complicado porque el operador between incluye siempre los límites de los valores. La función QUARTER_END debe por lo tanto devolver una indicación de tiempo, justo antes del primer día del próximo trimestre, si SALE_DATE tiene un componente de tiempo. Esa lógica puede estar oculta en la función.

Los ejemplos muestran las implementaciones de las funciones QUARTER_BEGIN y QUARTER_END para varias bases de datos.

Se pueden usar funciones auxiliares similares para otros períodos. La mayoría de ellas son menos complejas que el ejemplo de arriba, especialmente cuando se usan condiciones “mayor o igual que” (>=) y “menor que” (<) en lugar del operador between. Por supuesto, se podrían calcular las fechas límites dentro de tu aplicación si lo deseas.

    sale_date >= TRUNC(sysdate)
AND sale_date <  TRUNC(sysdate + INTERVAL '1' DAY)

Otra confusión común es comparar fechas como cadenas de caracteres como se muestra en el siguiente ejemplo PostgreSQL:

SELECT ...
  FROM sales
 WHERE TO_CHAR(sale_date, 'YYYY-MM-DD') = '1970-01-01'

El problema es, nuevamente, convertir SALE_DATE. Esa condición general­men­te se crea con la convicción de que no se pueden pasar diferentes tipos como número y cadenas de caracteres a la base de datos. Sin embargo, las variables Bind soportan todos los tipos. Eso significa por ejemplo usar un objeto java.util.Date como variable Bind. Se trata de otra ventaja de las variables Bind.

Si no se puede hacer eso, solamente se tiene que convertir el criterio de la búsqueda en vez de la columna de la tabla:

SELECT ...
  FROM sales
 WHERE sale_date = TO_DATE('1970-01-01', 'YYYY-MM-DD')

Esta sentencia puede usar un índice directo sobre SALE_DATE. Además, convierte la cadena de caracteres de entrada solamente una vez. La sentencia anterior debe convertir todas las fechas almacenadas dentro de la tabla antes de poder compararlas con el criterio de búsqueda.

En cualquier cambio que se hace, usando una variable Bind o convirtiendo el otro lado de la comparación, se puede introducir fácilmente un error si SALE_DATE tiene un componente de tiempo. En este caso, se debe usar de manera explícita la condición de rango:

SELECT ...
  FROM sales
 WHERE sale_date >= TO_DATE('1970-01-01', 'YYYY-MM-DD') 
   AND sale_date <  TO_DATE('1970-01-01', 'YYYY-MM-DD') 
                  + INTERVAL '1' DAY

Así pues, es aconsejable considerar siempre el uso de una condición de rango cuando se comparan fechas.

sale_date LIKE SYSDATE

filter( INTERNAL_FUNCTION(SALE_DATE)
   LIKE TO_CHAR(SYSDATE@!))

Si te gusta mi manera de explicar, te encantará mi libro.