de Martin LE TARNEC.

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.

DB2
CREATE FUNCTION quarter_begin(dt TIMESTAMP)
RETURNS TIMESTAMP
RETURN TRUNC(dt, 'Q')
CREATE FUNCTION quarter_end(dt TIMESTAMP)
RETURNS TIMESTAMP
RETURN TRUNC(dt, 'Q') + 3 MONTHS - 1 SECOND
MySQL
CREATE FUNCTION quarter_begin(dt DATETIME)
RETURNS DATETIME DETERMINISTIC
RETURN CONVERT
       (
         CONCAT
         ( CONVERT(YEAR(dt),CHAR(4))
         , '-'
         , CONVERT(QUARTER(dt)*3-2,CHAR(2))
         , '-01'
         )
       , datetime
       )
CREATE FUNCTION quarter_end(dt DATETIME)
RETURNS DATETIME DETERMINISTIC
RETURN DATE_ADD
       ( DATE_ADD ( quarter_begin(dt), INTERVAL 3 MONTH )
       , INTERVAL -1 MICROSECOND)
Oracle
CREATE FUNCTION quarter_begin(dt IN DATE) 
RETURN DATE
AS
BEGIN
   RETURN TRUNC(dt, 'Q');
END
CREATE FUNCTION quarter_end(dt IN DATE) 
RETURN DATE
AS
BEGIN
   -- the Oracle DATE type has seconds resolution
   -- subtract one second from the first 
   -- day of the following quarter
   RETURN TRUNC(ADD_MONTHS(dt, +3), 'Q') 
        - (1/(24*60*60));
END
PostgreSQL
CREATE FUNCTION quarter_begin(dt timestamp with time zone)
RETURNS timestamp with time zone AS $$
BEGIN
    RETURN date_trunc('quarter', dt);
END;
$$ LANGUAGE plpgsql
CREATE FUNCTION quarter_end(dt timestamp with time zone)
RETURNS timestamp with time zone AS $$
BEGIN
   RETURN   date_trunc('quarter', dt) 
          + interval '3 month'
          - interval '1 microsecond';
END;
$$ LANGUAGE plpgsql
SQL Server
CREATE FUNCTION quarter_begin (@dt DATETIME )
RETURNS DATETIME
BEGIN
  RETURN DATEADD (qq, DATEDIFF (qq, 0, @dt), 0)  
END
CREATE FUNCTION quarter_end (@dt DATETIME )
RETURNS DATETIME
BEGIN
  RETURN DATEADD
         ( ms
         , -3 
         , DATEADD(mm, 3, dbo.quarter_begin(@dt))
         );
END

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.

Sugerencia

Escribe sentencias para períodos ininterrumpidos como una condi­ción de rango explícita. Hazlo aún para un solo día. Por ejemplo, para la base de datos Oracle:

    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.

LIKE sobre tipos de fecha

La siguiente confusión es particularmente complicada:

sale_date LIKE SYSDATE

No parece ser una confusión a primera vista porque no usa ninguna función.

El operador LIKE, sin embargo, obliga a tener una comparación de las cadenas de caracteres. Dependiendo de la base de datos, produce un error o causa un tipo de conversión implícita en ambos lados. La sección “Predicate Information” del plan de ejecución muestra lo que hace la base de datos Oracle:

filter( INTERNAL_FUNCTION(SALE_DATE)
   LIKE TO_CHAR(SYSDATE@!))
La funcion INTERNAL_FUNCTION convierte el tipo de la columna SALE_DATE. Como efectos secundarios, previene también usar solamente un índice sobre DATE_COLUMN como cualquier otra función lo haría.

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

Acerca del autor

Foto de Markus Winand

Markus Winand enseña eficientemente SQL, en casa y online. Minimiza el tiempo de desarrollo utilizando moderno SQL y optimiza el tiempo de ejecución con indexación inteligente. Para ello también ha publicado el libro SQL Performance Explained.

“Use The Index, Luke” de Markus Winand se halla bajo licencia Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 Unported License.
Aspectos legales | Contacto | SIN GARANTÍA | Marcas | Privacy | CC-BY-NC-ND 3.0 licencia