Calcular fecha teniendo como valor los segundos desde una fecha dada.

Cuando partimos de una fecha inicial y queremos obtener una fecha final sumándole sólo segundos, en Oracle no disponemos de una función tipo fechafinal(fechainicial, segundos). Para ello podemos utilizar la propiedad de incrementar una fecha a partir de las unidades en días.

Por ejemplo a partir de la fecha "01/01/2011" le queremos sumar dos días, para ello podemos hacer:


SELECT TO_DATE('01/01/2011', 'DD/MM/YYYY') + 2 FROM DUAL


El resultado es "03/01/2011", a la fecha de partida se le han sumado 2 días.

Debido a que la función de sumar sólo tiene en cuenta las unidades en días, para sumar segundos deberemos sumar la fracción de días que representan los segundos. Partiendo de que en un día hay 86400 segundos (60 segundos en un minuto * 60 minutos en una hora * 24 horas en un día es igual a 86400 segundos en un día).
La fracción a sumar será SEGUNDOS / 86400.

Por ejemplo a partir de la fecha "01/01/2011" le queremos sumar 5 minutos (300 segundos), para ello:

SELECT TO_DATE('01/01/2011', 'DD/MM/YYYY HH24:MI:SS') + (300/86400) FROM DUAL

El resultado es "01/01/2011 00:05:00".

A partir de la versión 9i existen dos nuevos tipos de datos INTERVAL DAY TO SECOND y INTERVAL YEAR TO MONTH. Con estos tipos de datos se consigue clarificar la lectura del dato.

El ejemplo anterior quedaría como :

SELECT TO_DATE('01/01/2011', 'DD/MM/YYYY HH24:MI:SS') + (INTERVAL '5' MINUTE) FROM DUAL

El resultado es  "01/01/2011 00:05:00".
 

INTERVAL DAY TO SECOND

Este tipo de dato permite definir intervalos de fecha de días a segundos (con decimales sólo para los segundos), mostrando un formato del tipo D H:M:S  (D día, H horas, M minutos, S segundos con fracción)

Ejemplos:

SELECT INTERVAL '1' SECOND FROM DUAL              Muestra ->      0  0:0:1.0
SELECT INTERVAL '70.01' SECOND FROM DUAL       Muestra ->      0  0:1:10.01
SELECT INTERVAL '130' MINUTE FROM DUAL          Muestra ->      0  2:10:0.0
SELECT INTERVAL '25' HOUR FROM DUAL               Muestra ->      1  1:0:0.0
SELECT INTERVAL '3' DAY FROM DUAL                   Muestra ->      3  0:0:0.0

También se puede operar con ellos:

SELECT SYSTIMESTAMP, SYSTIMESTAMP - INTERVAL '10' MINUTE FROM DUAL

SELECT TO CHAR(SYSDATE,'dd-mm-yyyy hh24:mi:ss'), TO_CHAR( SYSDATE - INTERVAL '15' MINUTE,'dd-mm-yyyy hh24:mi:ss') FROM DUAL

INTERVAL YEAR TO MONTH

Este tipo de dato permite definir intervalos de fecha de años a meses, mostrando un formato del tipo YY-MM  (YY años, MM meses).

Ejemplos:

SELECT INTERVAL '30' MONTH FROM DUAL            Muestra ->    2-6
SELECT INTERVAL '30' YEAR FROM DUAL               Muestra ->    30-0

UTILIZAR LEVEL PARA DUPLICAR REGISTROS EN UNA CONSULTA

A veces nos interesa duplicar, triplicar o tener n copias de un mismo registro de la base de datos por cualquier motivo. Un ejemplo sería hacer n copias de la misma factura para un cliente.
Para abordar esto lo podemos hacer de muchas maneras, utilizando bucles en el código, con SQL sin intervención de ningún tipo de bucle u otras maneras que seguro sabréis ;).

En el siguiente ejemplo de enfoque de la solución he optado por SQL, ya que la motivación inicial era aplicarlo directamente en la consulta de obtención de datos para REPORTS 6.

Para simplificar el ejemplo supondremos que tenemos los siguientes datos en una tabla llamada Cliente con estos datos

Nombre Copias
----------- ---------
Jordi .......... 3
Pedro ........ 2
Elisa .......... 4

Utilizaremos la tabla DUAL para obtener una cantidad ilimitada de registros, pero para el ejemplo lo limitaremos a 5 registros.

SELECT LEVEL Registro FROM DUAL CONNECT BY LEVEL <= 5

Nota: cuidado con ejecutar SELECT LEVEL Registro FROM DUAL CONNECT BY LEVEL > 0, ya que la generación de la tabla temporal se comerá toda la memoria. (con 40.322.423 registros mi portatil se quedó sin memoria virtual)

Esto nos generará un conjunto de registros que quedaría así:

Registro
------------
1
2
3
4
5

Efectuamos la unión entre las dos tablas para obtener el conjunto de registros indicados en el campo copias de la tabla cliente.

SELECT c.nombre, c.copias, d.registro
FROM cliente c,
( SELECT LEVEL registro
FROM DUAL
CONNECT BY LEVEL <= 5 ) d WHERE c.copias >= d.registro

El resultado que obtenemos:

NOMBRE COPIAS REGISTRO
-------------- ------------ ----------------JORDI ............3................. 1
JORDI ............3................. 2
JORDI ........... 3................. 3
PEDRO ..........2................. 1
PEDRO ..........2................. 2
ELISA ...........4.................. 1
ELISA ...........4.................. 2
ELISA ...........4.................. 3
ELISA .......... 4.................. 4