Fechas: funciones y tipos en Oracle

TIPOS DE DATOS DE FECHAS

DATE
Rango desde 01/01/4712 AC hasta 31/12/9999 DC.
El tamaño es de 7 bytes.
El formato lo determina de manera implicita el parámetro NLS_TERRITORY y explicitamente el parámetro NLS_DATE_FORMAT
No contempla las fracciones de segundo ni la zona horaria.

TIMESTAMP [(fracciones_de_segundos)]
Admite fracciones de 0 a 9 dígitos, aunque por defecto son 6.
Dependiendo de la precisión el tamaño varía de 7 a 11 bytes.
El formato lo determina de manera implicita el parámetro NLS_TERRITORY y explicitamente el parámetro NLS_DATE_FORMAT
Contempla fracciones de segundo, pero no zona horaria.

TIMESTAMP [(fractional_seconds)] WITH TIME ZONE
Admite fracciones de 0 a 9 dígitos, aunque por defecto son 6.
El tamaño es fijo de 13 bytes
Contempla fracciones de segundo y zona horaria.

TIMESTAMP [(fractional_seconds)] WITH LOCAL TIME ZONE
Tiene una serie de consideraciones:
– La información es normalizada a la zona horaria cuando se guara en la base de datos
– Cuando la información es pedida a la base de datos es transformada a la zona horaria de la sesión

Admite fracciones de 0 a 9 dígitos, aunque por defecto son 6.
Dependiendo de la precisión el tamaño varía de 7 a 11 bytes.
El formato lo determina de manera implicita el parámetro NLS_TERRITORY y explicitamente el parámetro NLS_DATE_FORMAT
Contempla fracciones de segundo y zona horaria.

MASCARAS FORMATO DE FECHA

Las mascaras de tiempo se utilizan para indicar como se realizan las transformaciones de caracter a fecha u hora el formato.

Las mascaras, paras las transformaciones las podéis encontrar en el artículo: «Mascaras de tiempo en Oracle»

FUNCIONES DE CONVERSIÓN

Las conversiones de tipos de datos puede ser:
– IMPLÍCITAS: Realizada automáticamente por Oracle al asignar un valor de tipo date a un tipo CHAR, VARCHAR2, NCHAR, or NVARCHAR2. También al asignarle un CHAR, VARCHAR2, NCHAR, or NVARCHAR2 a un tipo DATE.
– EXPLÍCITAS: El usuario es quien la realiza

CONVERSIONES EXPLICITAS:

TO_CHAR (número | fecha [,’mascara’])
Convierte un número o fecha en una cadena de caracteres VARCHAR2 con el modelo de formato de la mascara.

TO_DATE (char [,’mascara’])
Convierte una cadena de caracteres representando una fecha en un valor de fecha según la mascara especificada..

TO_TIMESTAMP (CARACTER[, ‘mascara'[, ‘NLSPARAM’]]), TO_TIMESTAMP_TZ (CARACTER[, ‘mascara'[, ‘NLSPARAM’]])
Convierte una cadena de caracteres de tipos CHAR, VARCHAR2, NCHAR o NVARCHAR2 a un valor de tipo TIMESTAMP. Es TO_TIMESTAMP_TZ igual a TO_TIMESTAMP, aunque añadiendo el desplazamiento de la zona horaria en relación con GMT o UTC

SQL> select TO_TIMESTAMP ('10-Sep-02 14:10:10.123000',
                      'DD-Mon-RR HH24:MI:SS.FF')
      from dual


TO_TIMESTAMP('10-SEP-0214:10:10.123000','DD-MON-RRHH24:MI:SS.FF')
-----------------------------------------------------------------
10/09/02 14:10:10,123000000

OPERADORES ARITMÉTICOS DE FECHAS

Sumar números a fechas:

SQL> select to_date('01/01/2014', 'dd/mm/yyyy') + 1 from dual;
TO_DATE(
--------
02/01/14

Restar números a fechas:

SQL> select to_date('01/01/2014', 'dd/mm/yyyy') - 1 from dual;

TO_DATE(
--------
31/12/13

Restar dos fechas:

SQL> select to_date('01/01/2015', 'dd/mm/yyyy') -
          to_date('01/01/2014', 'dd/mm/yyyy')
     from dual

TO_DATE('01/01/2015','DD/MM/YYYY')-TO_DATE('01/01/2014','DD/MM/YYYY')
---------------------------------------------------------------------
                                                                  365

FUNCIONES FECHA Y HORAS

SYSDATE

Descripción:
devuelve la fecha y hora actual según la zona horaria configurada del sistema operativo del servidor.

Sintaxis:
SYSDATE

Ejemplos:

SELECT SYSDATE FROM DUAL; -> 06-JUL-13

CURRENT_DATE

Descripción:
devuelve la fecha y hora actual según la zona horaria configurada en la sesión.

Sintaxis:
CURRENT_DATE

Ejemplos:

SELECT CURRENT_DATE FROM DUAL; -> 06-JUL-13

SYSTIMESTAMP

Descripción:
devuelve el timestamp según la zona horaria configurada del sistema operativo del servidor.

Sintaxis:
SYSTIMESTAMP (TIMESTAMP_precision)

Ejemplos:

SELECT SYSTIMESTAMP(9) FROM DUAL;

   -> 06-JUL-13 02.05.08.079356000 AM +02:00

CURRENT_TIMESTAMP

Descripción:
devuelve el timestamp actual según la zona horaria configurada en la sesión.

Sintaxis:
CURRENT_TIMESTAMP (TIMESTAMP_precision)

Ejemplos:

SELECT CURRENT_TIMESTAMP(2) FROM DUAL; -> 06-JUL-13 02.06.20.63 AM +02:00

LOCALTIMESTAMP:

Descripción:
Devuelve la fecha y la hora actuales en la zona horaria de la sesión con un valor del tipo TIMESTAMP.

Sintaxis:
LOCALTIMESTAMP (TIMESTAMP_precision)

Ejemplos:

SELECT LOCALTIMESTAMP(2) FROM DUAL; -> 06-JUL-13 02.07.18.15 AM

ADD_MONTHS

Descripción:
Suma o resta a una fecha, un número n de meses.

Sintaxis:
add_months(fecha,n)

Ejemplos:

select add_months(sysdate,5) from dual; -> 06-DEC-13
select add_months(sysdate,-5) from dual; -> 06-FEB-13
select add_months(sysdate,1) from dual; -> 06-AUG-13

LAST_DAY

Descripción:
Devuelve la fecha del último día del mes que contiene fecha.

Sintaxis:
LAST_DAY (fecha)

Ejemplos:

select last_day(to_date('10/02/2013','DD/MM/YYYY')) from dual; -> 28-FEB-13
select last_day(sysdate) from dual; -> 31-JUL-13

MONTHS_BETWEEN

Descripción:
retorna el numero de meses entre las fechas enviadas como argumento. Ejemplo:

Sintaxis:
months_between(fecha1,fecha2)

Ejemplos:

select months_between(sysdate,to_date('10/02/2013','DD/MM/YYYY')) from dual;

    -> 4.87236858

NEXT_DAY

Descripción:
Devuelve una fecha correspondiente al primer día especificado en «dia» después de la fecha especificada

Sintaxis:
next_day(fecha,dia)

Ejemplos:

Busca el siguiente lunes a partir de hoy
select next_day(sysdate,'MONDAY') from dual; -> 08-JUL-13

EXTRACT

Descripción:
Devuelve la parte especificada por el primer argumento de una fecha, que corresponde al segundo parámetro. Puede extraerse el año (year), mes (month), día (day), hora (hour), minuto (minute), segundo (second), etc.

Sintaxis:
extract(parte,fecha)

Ejemplos:

select extract(month from sysdate) from dual; -> 7

ROUND

Descripción:
Cuando no se especifica ningún formato, devuelve la fecha del primer día del mes contenido en fecha. Si máscara=YEAR, encuentra el primer día del año.

Sintaxis:
ROUND (fecha [,máscara])

Ejemplos:

select round(sysdate,'YEAR') from dual; -> 01-JAN-14

TRUNC

Descripción:
Devuelve la fecha con la porción del día truncado en la unidad especificada por el modelo de formato fmt. Si se omite el formato, laf echa se trunca en el día más próximo.

Sintaxis:
TRUNC (fecha [,máscara])

Ejemplos:

SELECT sysdate, TRUNC(sysdate , 'Month')-1 FROM dual;

    -> 06-JUL-13 30-JUN-13

SYS_EXTRACT_UTC

Descripción:
Devuelve la fecha en UTC (zona horaria universal)

Sintaxis:
SYS_EXTRACT_UTC(datetime_with_timezone)

Ejemplos:

SELECT SYS_EXTRACT_UTC(TIMESTAMP '2013-07-05 11:30:00.00 -08:00') FROM DUAL;

 -> 05-JUL-13 07.30.00.000000000 PM

NUMTOYMINTERVAL

Descripción:
Convierte n a un literal del tipo INTERVAL YEAR TO MONTH.
Unidad de intervalo debe ser una de las siguientes cadenas: ‘DAY’, ‘HOUR’, ‘MINUTE’, ‘SECOND’

Sintaxis:
NUMTOYMINTERVAL(n,’unidad de intervalo’)

Ejemplos:

SELECT to_char(sysdate + numtoyminterval(5, 'MONTH'), 'MM/DD/YYYY') FROM dual; -> 12/06/2013
SELECT to_char(sysdate + numtoyminterval(2, 'YEAR'), 'MM/DD/YYYY') FROM dual; -> 07/06/2015

NUMTODSINTERVAL

Descripción:

Sintaxis:
NUMTODSINTERVAL(n,’unidad de intervalo’)

Ejemplos:

SELECT to_char(sysdate + numtodsinterval(2, 'HOUR'), 'HH:MI AM') FROM dual; -> 03:34 AM
SELECT to_char(sysdate + numtodsinterval(45, 'MINUTE'), 'HH:MI AM') FROM dual; -> 02:20 AM

TZ_OFFSET

Descripción:
devuelve el offset de zona horaria correspondiente al valor introducido.

Sintaxis:
TZ_OFFSET ( [‘time_zone_name’] ‘[+ | -] hh:mm’ ] [ SESSIONTIMEZONE] [DBTIMEZONE])

Ejemplos:

SELECT TZ_OFFSET('Europe/Madrid') FROM DUAL;