Anuncios en tutorial de programación PLSQL

martes, 12 de abril de 2016

Trabajando con fechas en PL/SQL: los tipos DATE, TIMESTAMP e INTERVAL

Los tipos PL/SQL DATE, TIMESTAMP e INTERVAL
Las fechas son un tipo de datos del PL/SQL considerablemente más complejo que un tipo carácter o un tipo numérico. Una fecha o momento de tiempo está compuesto de múltiples campos (año, mes, día, hora, minutos, etcétera) y, además, existen un buen número de normas para determinar si una fecha es válida o no (los años bisiestos, los cambios de hora, etcétera). Como consecuencia de todo esto, en PLSQL resulta habitual tener que:
  • Declarar constantes y variables de tipo fecha o tiempo.
  • Utilizar funciones para modificar dichas variables y mostrarlas en el formato deseado por el usuario.
  • Manipular fechas y tiempos para realizar cálculos variados.

Este artículo será el primero de una serie en los que explicaré todo lo que un programador PL/SQL necesita conocer para trabajar con los diferentes tipos de datos asociados con fechas y momentos de tiempo (DATE, TIMESTAMP e INTERVAL).

Los tipos DATE, TIMESTAMP e INTERVAL

Afortunadamente la base de datos Oracle y el PLSQL proporciona diferentes tipos de datos que permiten manejar fechas y momentos de tiempo, almacenando ambos tipos de información en un formato interno estándar.

Las bases de datos Oracle permiten utilizar tres tipos de datos diferentes para trabajar con fechas y momentos de tiempo:
  • DATE: este tipo de dato permite almacenar una fecha y un tiempo hasta el nivel de segundos. No incluye información sobre la zona horaria. Es el tipo de dato que más se utiliza para trabajar con fechas dentro de cualquier aplicación Oracle.
  • TIMESTAMP: se trata de un tipo de dato similar al DATE pero con dos diferencias clave, permiten almacenar y manipular momentos de tiempo hasta la mil millonésima de segundo (con una precisión de 9 decimales), y también es posible asociarle una zona horaria de tal manera que la base de datos Oracle tendrá en cuenta dicha zona horaria cuando manipulemos y realicemos cálculos utilizando este tipo de dato.
  • INTERVAL: mientras que los tipos DATE y TIMESTAMP indican un momento específico de tiempo, INTERVAL almacena y permite trabajar con duraciones de tiempo, siendo posible definir intervalos de tiempo en términos de años y meses, o de días y segundos.

A continuación os dejo un ejemplo de código PL/SQL donde se declaran diferentes variables que utilizan los tipos de dato DATE, TIMESTAMP e INTERVAL.

DECLARE
   l_hoy_date       DATE := SYSDATE;
   l_hoy_timestamp  TIMESTAMP := SYSTIMESTAMP;
   l_hoy_timetzone  TIMESTAMP WITH TIME ZONE
                    := SYSTIMESTAMP;
   l_interval_ym    INTERVAL YEAR (4) TO MONTH 
                    := '2011-11';
   l_interval_ds    INTERVAL DAY (2) TO SECOND 
                    := '15 00:30:44';
BEGIN
   null;
END;

Como comentario os diré que resulta poco usual que un programador de PLSQL tenga que utilizar los tipos TIMESTAMP e INTERVAL con zona horaria, algo que, por cierto, resulta algo complicado y se necesitan conocer algunas funcionalidades avanzadas.

¿Cómo escoger el tipo de dato fecha adecuado?

Ante la diversidad de tipos de dato fecha y momentos de tiempo que ofrece la base de datos Oracle, al escribir nuestro código PL/SQL puede resultarnos complicado decidirnos por un tipo de dato u otro. Estas son las normas que yo utilizo para decantarme por uno u otro:
  • Utilizar el tipo TIMESTAMP cuando es necesario controlar momentos de tiempo por debajo de la fracción de segundo.
  • En general es posible utilizar el tipo TIMESTAMP en lugar del tipo DATE, ya que la base de datos es capaz de distinguir cuando un TIMESTAMP no almacena fracciones de segundo, reservando sólo 7 bytes de almacenamiento para dicho dato, exactamente lo mismo que para un dato DATE. Cuando un TIMESTAMP contiene fracciones de segundo, entonces la base de datos Oracle necesita 11 bytes de almacenamiento.
  • Utilizar TIMESTAMP WITH TIME ZONE cuando sea necesario realizar un seguimiento de la zona horaria de la sesión en la que el dato fue introducido.
  • Utilizar TIMESTAMP WITH LOCAL TIME ZONE cuando la base de datos Oracle tenga que convertir automáticamente tiempos entre bases de datos y sesiones que operan bajo diferentes zonas horarias.
  • Utilizar DATE cuando sea necesario mantener la compatibilidad con una aplicación que fue escrita antes de que el tipo de dato TIMESTAMP fuera introducido.
  • En nuestro código PL/SQL siempre deberemos utilizar tipos de datos que se correspondan, o que al menos sean compatibles, con el tipo de dato asociado con el campo de la tabla que queramos almacenar en la variable correspondiente. Debemos ser conscientes de que si el campo de una tabla es tipo TIMESTAMP y lo almacenamos en una variable tipo DATE, podremos estar perdiendo información (en este caso la relativa a la zona horaria o a las fracciones de segundo).

En futuros artículos hablaré sobre las funciones que permiten obtener los valores de la fecha y el tiempo actual, las funciones para convertir fechas a caracteres y caracteres a fechas, y las funciones de truncado de fechas.

6 comentarios:

Aurora dijo...

Hola, buen articulo el que has publicado. Muchas Gracias. Por otra parte quisiera saber como se manejan los datos tipo timestamp en Oracle forms, pues tengo ese problema, que no puedo accesar al usuario OE desde el forms 10g pues el campo ORDER_DATE de la tabla ORDERS. Que se hacer, ¿Como se hace..? para manejar datos de tipo timestamp desde Oracle Developer forms? De antemano gracias.

José Luis Pérez dijo...

Aurora, ¿podrías explicar cual es exactamente el problema que tienes?

Aurora dijo...

Hola, José Luis, trataré de explicarte cual es mi problema, ... espero puedas ayudarme. Te lo voy a agradecer mucho.
Luego de conectarme a la base de datos, con el usuario OE , desde Oracle Forms, y asignarle las propiedades necesarias al módulo, lienzos y ventanas, ….. ejecuto el asistente de Bloques de Datos, y selecciono la tabla con la que se deseo trabajar, en esta práctica la TABLA ORDERS del esquema OE. Luego al dar click en el botón examinar aparece el siguiente error: FRM-10095: Fallo de afirmación en iewbdbc_oracle_to_id, en C:\forms\1012\src\ie\iewbdb.c:724.
De aquí no se que hacer,…. Al darle click en el botón ayuda del mensaje de error que aparece me envía a < FRM-10095: Assertion failed in %s, at %s:%d. Cause: An internal inconsistency was detected. Action: Contact an Oracle support representative, and proceed with caution. >
En internet no encuentro información sobre este error, nada más he leído que se debe a que la columna ORDER_DATE es de tipo TIMESTAMP.
He probado con las demás tablas del usuario OE, como de otros usuarios y no hay ningún problema. Ya estuve revisando que los demás usuarios, en todas las tablas, NO contienen campos con este tipo de datos.
Entonces, la interrogante es como puedo hacer para trabajar con este tipo de datos, desde Oracle Developer Forms, ¿Qué puedo hacer?. ¿Hay alguna forma de superar este fallo?. ¿Se tiene que configurar algo, en Oracle Developer para que acepte trabajar con este tipo de datos?
Bueno, de antemano gracias por su ayuda.

datos:
Usuario OE
Tabla ORDERS
Columna: ORDER_DATE
Tipo de Datos: TIMESTAMP(6) WITH LOCAL TIME ZONE

José Luis Pérez dijo...

Hola Aurora, no me he encontrado nunca con un problema similar al tuyo. Prueba a buscar en Metalink para ver si Oracle da la solución a este problema. Si no ves nada probaría a comprobar si la versión que usas de Oracle Developers Form es 100% compatible con la versión de la base de datos Oracle a la que accedes.

Aurora dijo...

Hola, gracias por responder. Pues fíjate que la base de datos es la 11g y el Oracle Developer Suite es la 10g. Como te comentaba, estoy iniciandome en esto,... por lo que no se como comprobar la compatibilidad del Developer y de la Base de Datos. Puedo conectarme con todos los usuarios, porque no tienen columnas con tipo TIMESTAMP, pero no puedo accesar a la tabla ORDERS, del usuario OE, pues tiene una columna con este tipo. Bueno, espero me orientes sobre como comprobar la compatibilidad entre la base y el developer.
Las descargas la realicé de: http://www.oracle.com/technetwork/database/enterprise-edition/downloads/112010-win32soft-098987.html
Descargué los 2 archivos zip: Oracle Database 11g Release 2 (11.2.0.1.0) for Microsoft Windows (32-bit)
El Oracle Developer lo descargué de:
http://www.oracle.com/technetwork/developer-tools/developer-suite/downloads/index.html?ssSourceSiteId=otnes
el que incluye el Forms y el Designer:
Oracle Developer Suite 10g (10.1.2.0.2) (including Forms and Designer).
Bueno, gracias de antemano.

José Luis Pérez dijo...

Como te dije, creo que lo mejor es que consultes en Metalink (https://support.oracle.com/).