INTERVALs YEAR & MONTH y DATEs en Teradata (y Oracle).

Teradata implementa los tipos intervalos (‘INTERVALs’), que son SQL ANSI 2008. Como su nombre indica, definen intervalos (periodos) de tiempo. Los tipos INTERVAL se pueden separar en dos familias principales: AÑOS y MESES por un lado, y DÍAS, HORAS, MINUTOS y SEGUNDOS por otra (además, como ocurre en el mundo real, estas familias no se llevan muy bien, pero esa es otra historia…)

Si nos centramos en los primeros (YEARs y MONTHs), probablemente lo primero que nos vendrá a la cabeza será su relación con los tipos DATE y su aritmética. En efecto, en la documentación de Teradata vienen ejemplos de cómo sumar DATEs con INTERVALs sin más:

Example 4: date_time_expression
In this example, the date_time_expression is an interval_expression added to a date_time_term.
Note that you can only add these terms —subtraction of a date_time_term from an
interval_expression is not permitted-.
SELECT INTERVAL ’20’ YEAR + CURRENT_DATE;

Pero la cosa no es tan fácil como parece, ya que aunque cosas como:

 BTEQ -- Enter your SQL request or BTEQ command:
SELECT CURRENT_DATE, CURRENT_DATE + INTERVAL '1' YEAR;

 *** Query completed. One row found. 2 columns returned.
 *** Total elapsed time was 1 second.

Current Date  (Current Date+ 1)
------------  -----------------
  2011-06-13         2012-06-13

funcionan bien, enseguida nos metemos en problemas cuando intentamos:

 BTEQ -- Enter your SQL request or BTEQ command:
SELECT CAST('2012-02-29' AS DATE);

 *** Query completed. One row found. One column returned.
 *** Total elapsed time was 1 second.

'2012-02-29'
------------
  2012-02-29

 BTEQ -- Enter your SQL request or BTEQ command:
SELECT CAST('2012-02-29' AS DATE) + CAST('1' AS INTERVAL YEAR);

 *** Failure 2665 Invalid date.
                Statement# 1, Info =0
 *** Total elapsed time was 1 second.

En efecto: el 29/02/2012 (2012 es un año bisiesto) produce un error al sumarle un INTERVAL ‘1’ YEAR.

Si nos fijamos en los INTERVAL MONTH, nos pasa exactamente lo mismo:

 BTEQ -- Enter your SQL request or BTEQ command:
SELECT CURRENT_DATE, CURRENT_DATE + INTERVAL '1' MONTH;

 *** Query completed. One row found. 2 columns returned.
 *** Total elapsed time was 1 second.

Current Date  (Current Date+ 1)
------------  -----------------
  2011-06-13         2011-07-13

funciona bien, pero:

 BTEQ -- Enter your SQL request or BTEQ command:
SELECT CAST('2011-01-31' AS DATE) + CAST('1' AS INTERVAL MONTH);

 *** Failure 2665 Invalid date.
                Statement# 1, Info =0
 *** Total elapsed time was 1 second.

produce un error de fecha inválida (‘Invalid date‘).

Esto se reproduce en todas las ocasiones en las que el mes de la fecha resultante de la adición del intervalo no tiene el día del mes de la fecha orígen:

 BTEQ -- Enter your SQL request or BTEQ command:
SELECT CAST('2011-03-31' AS DATE) + CAST('1' AS INTERVAL MONTH);

 *** Failure 2665 Invalid date.
                Statement# 1, Info =0
 *** Total elapsed time was 1 second.

En otras palabras, fallará para todos los días ’31’ a los que se le sume un intervalo de meses que resulte en un mes de 30 días. Por supuesto, el caso de Febrero es el más obvio. Además febrero presenta la particularidad de ocasionar los mismos problemas con los INTERVAL YEAR y los años bisiestos, como ya hemos visto.

Todo esto nos lleva a concluir que cuando efectuamos aritmética de fechas (DATE) con tipos INTERVAL, éstos se aplican simplemente incrementando la parte de ‘año’ de la fecha en el caso de los YEAR y la parte de ‘mes’ de la misma para los MONTH, sin tener en cuenta la parte de ‘días’, por lo que se pueden producir los errores vistos más arriba.

Es por ello que siempre que necesitemos operar con fechas (DATEs) y tengamos que agregar o restar meses o años, lo mejor es recurrir a la siempre amiga ADD_MONTHS(), que además pertenece al estándar ANSI SQL 2008:

 
BTEQ -- Enter your SQL request or BTEQ command:
SELECT ADD_MONTHS(CAST('2012-02-29' AS DATE), 12);

 *** Query completed. One row found. One column returned.
 *** Total elapsed time was 1 second.

ADD_MONTHS('2012-02-29', 12)
----------------------------
                  2013-02-28

 BTEQ -- Enter your SQL request or BTEQ command:
SELECT ADD_MONTHS(CAST('2011-01-31' AS DATE), 1);

 *** Query completed. One row found. One column returned.
 *** Total elapsed time was 1 second.

ADD_MONTHS('2011-01-31', 1)
---------------------------
                 2011-02-28

Aviso para los ‘Oracleros’: el comportamiento en Oracle es exactamente el mismo (aunque los tipos allí son más estrictos: son siempre INTERVAL YEAR TO MONTH):

CARLOS@XE.localhost> SELECT TO_DATE('2011-03-31', 'YYYY-MM-DD') +
  2         CAST('0-1' AS INTERVAL YEAR(1) TO MONTH)
  3  FROM DUAL;
SELECT TO_DATE('2011-03-31', 'YYYY-MM-DD') +
                                           *
ERROR en línea 1:
ORA-01839: fecha incorrecta para el mes especificado

CARLOS@XE.localhost> SELECT TO_DATE('2011-03-30', 'YYYY-MM-DD') +
  2         CAST('0-1' AS INTERVAL YEAR(1) TO MONTH)
  3  FROM DUAL;

TO_DATE('2011-03-30
-------------------
2011/04/30 00:00:00

CARLOS@XE.localhost> SELECT TO_DATE('2012-02-29', 'YYYY-MM-DD') +
  2         CAST('1-0' AS INTERVAL YEAR(1) TO MONTH)
  3  FROM DUAL;
SELECT TO_DATE('2012-02-29', 'YYYY-MM-DD') +
                                           *
ERROR en línea 1:
ORA-01839: fecha incorrecta para el mes especificado

CARLOS@XE.localhost> SELECT TO_DATE('2012-02-28', 'YYYY-MM-DD') +
  2         CAST('1-0' AS INTERVAL YEAR(1) TO MONTH)
  3  FROM DUAL;

TO_DATE('2012-02-28
-------------------
2013/02/28 00:00:00

Saludos.

Carlos.

3 respuestas a INTERVALs YEAR & MONTH y DATEs en Teradata (y Oracle).

  1. Óscar de la Torre dice:

    También está muy bien cuando intentas aplicar una función de agregación:

    SQL> l
      1  with t as (select interval '1' second s from dual
      2  union all
      3  select interval '2' second from dual)
      4* select sum(s) from t
    SQL> /
    select sum(s) from t
               *
    ERROR en linea 4:
    ORA-00932: tipos de dato inconsistentes: se esperaba NUMBER se ha obtenido INTERVAL DAY TO SECOND
  2. CarlosAL dice:

    Cierto.

    SUM() no funciona con INTERVAL.

    Habría que hacer algo así como:

    CARLOS@XE.localhost> with t as
      2  (select interval '1' second(2,0) s1,
      3          interval '2' second(2,0) s2
      4     from dual)
      5  select CAST((s1+s2) AS INTERVAL DAY TO SECOND(0)) from t
      6  ;
    
    CAST((S1+S2)ASINTERVALDAYTOSECOND(0))
    -------------------------------------------------------------
    +00 00:00:03
    

    Lo cual no es como para tirar cohetes (aparte de ser operaciones ‘cpu-intensive’ y dejar frita la CPU).

    Saludos.

    Carlos.

  3. […] y la convierta después a INTERVAL MONTH. Este cálculo (al igual que lo que vimos aquí) no tiene en cuenta la parte ‘días’ de la fecha, sólo la diferencia de meses: de ahí […]

Deja un comentario

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

A %d blogueros les gusta esto: