Oracle Database
fechas
Buscar..
Fechas de generación sin componente de tiempo
Todos los DATE s tienen un componente de tiempo; sin embargo, es habitual almacenar fechas que no necesitan incluir información de tiempo con las horas / minutos / segundos establecidos en cero (es decir, medianoche).
Use un literal ANSI DATE (utilizando el formato de fecha ISO 8601 ):
SELECT DATE '2000-01-01' FROM DUAL;
Conviértalo de un literal de cadena usando TO_DATE() :
SELECT TO_DATE( '2001-01-01', 'YYYY-MM-DD' ) FROM DUAL;
(Puede encontrar más información sobre los modelos de formato de fecha en la documentación de Oracle).
o:
SELECT TO_DATE(
'January 1, 2000, 00:00 A.M.',
'Month dd, YYYY, HH12:MI A.M.',
'NLS_DATE_LANGUAGE = American'
)
FROM DUAL;
(Si está convirtiendo términos específicos del idioma, como los nombres de los meses, es una buena práctica incluir el tercer parámetro nlsparam en la función TO_DATE() y especificar el idioma que se espera).
Generando fechas con un componente de tiempo
Conviértalo de un literal de cadena usando TO_DATE() :
SELECT TO_DATE( '2000-01-01 12:00:00', 'YYYY-MM-DD HH24:MI:SS' ) FROM DUAL;
O use un literal de TIMESTAMP :
CREATE TABLE date_table(
date_value DATE
);
INSERT INTO date_table ( date_value ) VALUES ( TIMESTAMP '2000-01-01 12:00:00' );
Oracle implícitamente emitirá un TIMESTAMP a una DATE cuando lo almacene en una columna DATE de una tabla; sin embargo, puede CAST() explícitamente CAST() el valor a una DATE :
SELECT CAST( TIMESTAMP '2000-01-01 12:00:00' AS DATE ) FROM DUAL;
El formato de una fecha
En Oracle, un tipo de datos DATE no tiene un formato; cuando Oracle envía una DATE al programa cliente (SQL / Plus, SQL / Developer, Toad, Java, Python, etc.) enviará 7- u 8 bytes que representan la fecha.
Una DATE que no se almacena en una tabla (es decir, generada por SYSDATE y que tiene "tipo 13" cuando se usa el comando DUMP() ) tiene 8 bytes y tiene la estructura (los números a la derecha son la representación interna de 2012-11-26 16:41:09 ):
BYTE VALUE EXAMPLE
---- ------------------------------- --------------------------------------
1 Year modulo 256 220
2 Year multiples of 256 7 (7 * 256 + 220 = 2012)
3 Month 11
4 Day 26
5 Hours 16
6 Minutes 41
7 Seconds 9
8 Unused 0
Una DATE que se almacena en una tabla ("tipo 12" cuando se usa el comando DUMP() ) tiene 7 bytes y tiene la estructura (los números a la derecha son la representación interna de 2012-11-26 16:41:09 ):
BYTE VALUE EXAMPLE
---- ------------------------------- --------------------------------------
1 ( Year multiples of 100 ) + 100 120
2 ( Year modulo 100 ) + 100 112 ((120-100)*100 + (112-100) = 2012)
3 Month 11
4 Day 26
5 Hours + 1 17
6 Minutes + 1 42
7 Seconds + 1 10
Si desea que la fecha tenga un formato específico, deberá convertirlo a algo que tenga un formato (es decir, una cadena). El cliente SQL puede hacer esto implícitamente o puede convertir explícitamente el valor en una cadena usando TO_CHAR( date, format_model, nls_params ) .
Convertir fechas a una cadena
Use TO_CHAR( date [, format_model [, nls_params]] ) :
(Nota: si no se proporciona un modelo de formato, entonces se NLS_DATE_FORMAT parámetro de sesión NLS_DATE_FORMAT como el modelo de formato predeterminado ; esto puede ser diferente para cada sesión, por lo que no debe confiarse. Es una buena práctica especificar siempre el modelo de formato).
CREATE TABLE table_name (
date_value DATE
);
INSERT INTO table_name ( date_value ) VALUES ( DATE '2000-01-01' );
INSERT INTO table_name ( date_value ) VALUES ( TIMESTAMP '2016-07-21 08:00:00' );
INSERT INTO table_name ( date_value ) VALUES ( SYSDATE );
Entonces:
SELECT TO_CHAR( date_value, 'YYYY-MM-DD' ) AS formatted_date FROM table_name;
Salidas:
FORMATTED_DATE
--------------
2000-01-01
2016-07-21
2016-07-21
Y:
SELECT TO_CHAR(
date_value,
'FMMonth d yyyy, hh12:mi:ss AM',
'NLS_DATE_LANGUAGE = French'
) AS formatted_date
FROM table_name;
Salidas:
FORMATTED_DATE
-----------------------------
Janvier 01 2000, 12:00:00 AM
Juillet 21 2016, 08:00:00 AM
Juillet 21 2016, 19:08:31 PM
Configuración del modelo de formato de fecha predeterminado
Cuando Oracle se convierte implícitamente de una DATE a una cadena o viceversa (o cuando TO_CHAR() o TO_DATE() se llaman explícitamente sin un modelo de formato), el parámetro de sesión NLS_DATE_FORMAT se usará como el modelo de formato en la conversión. Si el literal no coincide con el modelo de formato, se generará una excepción.
Puedes revisar este parámetro usando:
SELECT VALUE FROM NLS_SESSION_PARAMETERS WHERE PARAMETER = 'NLS_DATE_FORMAT';
Puede establecer este valor dentro de su sesión actual usando:
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
(Nota: esto no cambia el valor para ningún otro usuario).
Si confía en NLS_DATE_FORMAT para proporcionar la máscara de formato en TO_DATE() o TO_CHAR() , no debería sorprenderse cuando sus consultas se interrumpen si este valor se modifica.
Cómo cambiar las fechas de visualización de SQL / Plus o SQL Developer
Cuando SQL / Plus o SQL Developer muestran fechas, realizarán una conversión implícita a una cadena utilizando el modelo de formato de fecha predeterminado (consulte el ejemplo Configuración del modelo de formato de fecha predeterminado).
Puede cambiar la forma en que se muestra una fecha cambiando el parámetro NLS_DATE_FORMAT .
Aritmética de fechas: diferencia entre fechas en días, horas, minutos y / o segundos
En Oracle, la diferencia (en días y / o fracciones de los mismos) entre dos DATE se puede encontrar mediante la resta:
SELECT DATE '2016-03-23' - DATE '2015-12-25' AS difference FROM DUAL;
Muestra el número de días entre las dos fechas:
DIFFERENCE
----------
89
Y:
SELECT TO_DATE( '2016-01-02 01:01:12', 'YYYY-MM-DD HH24:MI:SS' )
- TO_DATE( '2016-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS' )
AS difference
FROM DUAL
Produce la fracción de días entre dos fechas:
DIFFERENCE
----------
1.0425
La diferencia en horas, minutos o segundos puede encontrarse multiplicando este número por 24 , 24*60 o 24*60*60 respectivamente.
El ejemplo anterior se puede cambiar para obtener los días, horas, minutos y segundos entre dos fechas utilizando:
SELECT TRUNC( difference ) AS days,
TRUNC( MOD( difference * 24, 24 ) ) AS hours,
TRUNC( MOD( difference * 24*60, 60 ) ) AS minutes,
TRUNC( MOD( difference * 24*60*60, 60 ) ) AS seconds
FROM (
SELECT TO_DATE( '2016-01-02 01:01:12', 'YYYY-MM-DD HH24:MI:SS' )
- TO_DATE( '2016-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS' )
AS difference
FROM DUAL
);
(Nota: TRUNC() se usa en lugar de FLOOR() para manejar correctamente las diferencias negativas.)
Salidas:
DAYS HOURS MINUTES SECONDS
---- ----- ------- -------
1 1 1 12
El ejemplo anterior también se puede resolver convirtiendo la diferencia numérica en un intervalo usando NUMTODSINTERVAL() :
SELECT EXTRACT( DAY FROM difference ) AS days,
EXTRACT( HOUR FROM difference ) AS hours,
EXTRACT( MINUTE FROM difference ) AS minutes,
EXTRACT( SECOND FROM difference ) AS seconds
FROM (
SELECT NUMTODSINTERVAL(
TO_DATE( '2016-01-02 01:01:12', 'YYYY-MM-DD HH24:MI:SS' )
- TO_DATE( '2016-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS' ),
'DAY'
) AS difference
FROM DUAL
);
Aritmética de fechas: diferencia entre fechas en meses o años
La diferencia en meses entre dos fechas se puede encontrar utilizando el MONTHS_BETWEEN( date1, date2 ) :
SELECT MONTHS_BETWEEN( DATE '2016-03-10', DATE '2015-03-10' ) AS difference FROM DUAL;
Salidas:
DIFFERENCE
----------
12
Si la diferencia incluye meses parciales, devolverá la fracción del mes según que haya 31 días en cada mes:
SELECT MONTHS_BETWEEN( DATE '2015-02-15', DATE '2015-01-01' ) AS difference FROM DUAL;
Salidas:
DIFFERENCE
----------
1.4516129
Debido a que MONTHS_BETWEEN supone 31 días al mes cuando puede haber menos días al mes, esto puede MONTHS_BETWEEN valores diferentes para las diferencias que abarcan los límites entre los meses.
Ejemplo:
SELECT MONTHS_BETWEEN( DATE'2016-02-01', DATE'2016-02-01' - INTERVAL '1' DAY ) AS "JAN-FEB",
MONTHS_BETWEEN( DATE'2016-03-01', DATE'2016-03-01' - INTERVAL '1' DAY ) AS "FEB-MAR",
MONTHS_BETWEEN( DATE'2016-04-01', DATE'2016-04-01' - INTERVAL '1' DAY ) AS "MAR-APR",
MONTHS_BETWEEN( DATE'2016-05-01', DATE'2016-05-01' - INTERVAL '1' DAY ) AS "APR-MAY"
FROM DUAL;
Salida:
JAN-FEB FEB-MAR MAR-APR APR-MAY
------- ------- ------- -------
0.03226 0.09677 0.03226 0.06452
La diferencia en años se puede encontrar al dividir la diferencia del mes entre 12.
Extraiga los componentes del año, mes, día, hora, minuto o segundo de una fecha
Los componentes de año, mes o día de un tipo de datos DATE se pueden encontrar utilizando EXTRACT( [ YEAR | MONTH | DAY ] FROM datevalue )
SELECT EXTRACT (YEAR FROM DATE '2016-07-25') AS YEAR,
EXTRACT (MONTH FROM DATE '2016-07-25') AS MONTH,
EXTRACT (DAY FROM DATE '2016-07-25') AS DAY
FROM DUAL;
Salidas:
YEAR MONTH DAY
---- ----- ---
2016 7 25
Los componentes de tiempo (hora, minuto o segundo) se pueden encontrar por:
- Uso de
CAST( datevalue AS TIMESTAMP )para convertir laDATEen unTIMESTAMPy luego usarEXTRACT( [ HOUR | MINUTE | SECOND ] FROM timestampvalue ); o - Usando
TO_CHAR( datevalue, format_model )para obtener el valor como una cadena.
Por ejemplo:
SELECT EXTRACT( HOUR FROM CAST( datetime AS TIMESTAMP ) ) AS Hours,
EXTRACT( MINUTE FROM CAST( datetime AS TIMESTAMP ) ) AS Minutes,
EXTRACT( SECOND FROM CAST( datetime AS TIMESTAMP ) ) AS Seconds
FROM (
SELECT TO_DATE( '2016-01-01 09:42:01', 'YYYY-MM-DD HH24:MI:SS' ) AS datetime FROM DUAL
);
Salidas:
HOURS MINUTES SECONDS
----- ------- -------
9 42 1
Zonas horarias y horario de verano
El tipo de datos DATE no controla las zonas horarias ni los cambios en el horario de verano.
Ya sea:
- use el tipo de datos
TIMESTAMP WITH TIME ZONE; o - Manejar los cambios en la lógica de su aplicación.
Una DATE se puede almacenar como hora universal coordinada (UTC) y se puede convertir a la zona horaria de la sesión actual de esta manera:
SELECT FROM_TZ(
CAST(
TO_DATE( '2016-01-01 12:00:00', 'YYYY-MM-DD HH24:MI:SS' )
AS TIMESTAMP
),
'UTC'
)
AT LOCAL AS time
FROM DUAL;
Si ejecuta ALTER SESSION SET TIME_ZONE = '+01:00'; entonces la salida es:
TIME
------------------------------------
2016-01-01 13:00:00.000000000 +01:00
y ALTER SESSION SET TIME_ZONE = 'PST'; entonces la salida es:
TIME
------------------------------------
2016-01-01 04:00:00.000000000 PST
Leap Seconds
Oracle no maneja segundos de salto . Consulte la nota 2019397.2 y 730795.1 de My Oracle Support para obtener más detalles.
Obtención del día de la semana
Puede usar TO_CHAR( date_value, 'D' ) para obtener el día de la semana.
Sin embargo, esto depende del parámetro de sesión NLS_TERRITORY :
ALTER SESSION SET NLS_TERRITORY = 'AMERICA'; -- First day of week is Sunday
SELECT TO_CHAR( DATE '1970-01-01', 'D' ) FROM DUAL;
Salidas 5
ALTER SESSION SET NLS_TERRITORY = 'UNITED KINGDOM'; -- First day of week is Monday
SELECT TO_CHAR( DATE '1970-01-01', 'D' ) FROM DUAL;
Salidas 4
Para hacer esto independientemente de la configuración de NLS , puede truncar la fecha hasta la medianoche del día actual (para eliminar cualquier fracción de días) y restar la fecha truncada al inicio de la iso-semana actual (que siempre comienza el lunes):
SELECT TRUNC( date_value ) - TRUNC( date_value, 'IW' ) + 1 FROM DUAL