Importando fechas julianas a Teradata.

Actualmente estoy trabajando en un proceso de incorporación de datos de una Base de datos OLTP Oracle a un Data Warehouse Teradata. La cosa no parecía muy complicada hasta que nos hemos dado cuenta que dicha base de datos Oracle es a su vez resultado de una migración -y no muy bien hecha- de un DB2. Entre otras particularidades nos hemos encontrado con que hay tablas sin ‘primary key‘ y que las tablas y columnas tienen nombres restringidos a 8 dígitos (herencia DB2, suponemos). También que las tablas presentan cláusulas de almacenamiento (‘STORAGE’) con ‘INITIAL’ 1K, 2K, 3K,… (¡Hey! ¡Estamos en 2009! ¿alguien ha oído hablar de los LMT’s?) lo que para un ‘datablock’ de 8K (suponemos) resulta ser un tanto ‘surrealista’.

Pero lo mejor es que todas las fechas aparecen como NUMBER(7). Al preguntar, nos dicen que son fechas ‘en juliano’ (‘julian date‘).

El calendario juliano es un calendario en el que se cuentan los días desde el 1 de enero del 4713 antes de Cristo a mediodía. Así el 14/07/2009 será el 2455027.

Oracle maneja las fechas julianas sin problemas mediante el formato ‘J’ de las funciones TO_CHAR y TO_DATE:

CARLOS@XE.localhost> SELECT TO_CHAR(SYSDATE, 'J') FROM DUAL;

TO_CHAR
-------
2455027

CARLOS@XE.localhost> SELECT TO_DATE('2455027', 'J') FROM DUAL;

TO_DATE(
--------
14/07/09

Con lo que la incorporación de enteros (NUMBER(7)) como fechas julianas a Oracle no presentaría mayor problema que el uso de un TO_DATE().

Pero Teradata es otra cuestión. Teradata no presenta algo análogo al formato ‘J’ de Oracle y hay que pensar cómo convertir esos NUMBER(7) a fechas.

El primer intento es obvio, pero fallido:

SELECT CAST('-4713/01/01' AS DATE FORMAT 'YYYY/MM/DD') + 2455027;
 *** Failure 2665 Invalid date.
                Statement# 1, Info =0
 *** Total elapsed time was 1 second.

De hecho, Teradata no soporta fechas anteriores al 1 de enero de 01 (al menos directamente):

Internally, Teradata Database stores each DATE value as a four-byte signed integer using the following formula:

(YEAR – 1900) * 10000 + (MONTH * 100) + DAY

where the YEAR, MONTH, and DAY components, defined appropriately for the Gregorian calendar, have the following range of values:

Range of values
Component Minimum Maximum
YEAR 1 9999
MONTH 1 12
DAY 1 28, 29, 30, or 31 (depending on the month and year)

En efecto:

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
SELECT CAST((1 - 1900) * 10000 + (1 * 100) + 1 AS DATE);

SELECT CAST((1 - 1900) * 10000 + (1 * 100) + 1 AS DATE);

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

((((1-1900)*10000)+(1*100))+1)
------------------------------
                      01/01/01

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
SELECT CAST((1 - 1900) * 10000 + (1 * 100) + 1 AS DATE) - 1;

SELECT CAST((1 - 1900) * 10000 + (1 * 100) + 1 AS DATE) - 1;
 *** Failure 2665 Invalid date.
                Statement# 1, Info =0
 *** Total elapsed time was 1 second.

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
SELECT CAST('0001/01/01' AS DATE FORMAT 'YYYY/MM/DD');

SELECT CAST('0001/01/01' AS DATE FORMAT 'YYYY/MM/DD');

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

'0001/01/01'
------------
  0001/01/01

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
SELECT CAST('0001/01/01' AS DATE FORMAT 'YYYY/MM/DD') - 1;

SELECT CAST('0001/01/01' AS DATE FORMAT 'YYYY/MM/DD') - 1;
 *** Failure 2665 Invalid date.
                Statement# 1, Info =0
 *** Total elapsed time was 1 second.

Entonces ¿cómo nos las ingeniamos? La respuesta está en algo llamado traslación. En vez de irnos al 1 de enero de 4713 antes de Cristo como origen de la aritmética de fechas, podemos hacer una traslación a -digamos- el 1 de enero de 1900: tomamos la fecha juliana de ese día, que resulta ser el número 2415021, y sacamos la diferencia con la fecha juliana recibida sabiendo la fecha base tomada. (También es bueno recordar que Teradata guarda este 1 de enero de 1900 como 101).

Así para nuestro 14/07/2009 (2455027 en juliano) tendremos que:

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
SELECT CAST(101 AS DATE) + (CAST(2455027 AS INTEGER) - 2415021) (FORMAT 'DD/MM/YYYY');

SELECT CAST(101 AS DATE) + (CAST(2455027 AS INTEGER) - 2415021) (FORMAT 'DD/MM/YYYY');

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

(101+(2455027-2415021))
-----------------------
             14/07/2009

Y con esta técnica podemos incorporar las enrevesadas fechas julianas NUMBER(7) de origen a simples fechas DATE que todo el mundo entiende.

Saludos.

Carlos.

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: