Oracle Database
Date
Ricerca…
Generazione di date senza componente orario
Tutti i DATE hanno un componente orario; tuttavia, è consuetudine memorizzare le date che non devono includere informazioni sull'ora con le ore / minuti / secondi impostati su zero (ad es. a mezzanotte).
Utilizza un valore letterale ANSI DATE (utilizzando il formato data ISO 8601 ):
SELECT DATE '2000-01-01' FROM DUAL;
Converti da una stringa letterale utilizzando TO_DATE() :
SELECT TO_DATE( '2001-01-01', 'YYYY-MM-DD' ) FROM DUAL;
(Ulteriori informazioni sui modelli di formato della data sono disponibili nella documentazione di 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;
(Se si convertono termini specifici della lingua, come i nomi dei mesi, è buona norma includere il terzo parametro nlsparam nella funzione TO_DATE() e specificare la lingua da aspettarsi.)
Generazione di date con un componente temporale
Converti da una stringa letterale utilizzando TO_DATE() :
SELECT TO_DATE( '2000-01-01 12:00:00', 'YYYY-MM-DD HH24:MI:SS' ) FROM DUAL;
Oppure usa un letterale TIMESTAMP :
CREATE TABLE date_table(
date_value DATE
);
INSERT INTO date_table ( date_value ) VALUES ( TIMESTAMP '2000-01-01 12:00:00' );
Oracle trasmetterà implicitamente un TIMESTAMP a un DATE quando lo memorizza in una colonna DATE di una tabella; tuttavia puoi CAST() esplicitamente il valore di una DATE :
SELECT CAST( TIMESTAMP '2000-01-01 12:00:00' AS DATE ) FROM DUAL;
Il formato di una data
In Oracle, un tipo di dati DATE non ha un formato; quando Oracle invia un DATE al programma client (SQL / Plus, SQL / Developer, Toad, Java, Python, ecc.) invierà 7 o 8 byte che rappresentano la data.
Una DATE che non è memorizzata in una tabella (cioè generata da SYSDATE e che ha "tipo 13" quando si usa il comando DUMP() ) ha 8 byte e ha la struttura (i numeri sulla destra sono la rappresentazione interna di 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 che è memorizzata in una tabella ("tipo 12" quando si usa il comando DUMP() ) ha 7 byte e ha la struttura (i numeri sulla destra sono la rappresentazione interna di 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
Se vuoi che la data abbia un formato specifico, dovrai convertirla in qualcosa che ha un formato (cioè una stringa). Il client SQL può implicitamente farlo o è possibile convertire esplicitamente il valore in una stringa utilizzando TO_CHAR( date, format_model, nls_params ) .
Conversione delle date in una stringa
Utilizza TO_CHAR( date [, format_model [, nls_params]] ) :
(Nota: se non viene fornito un modello di formato, il parametro della sessione NLS_DATE_FORMAT verrà utilizzato come modello di formato predefinito , che può essere diverso per ogni sessione, quindi non dovrebbe essere invocato.È buona norma specificare sempre il modello di 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 );
Poi:
SELECT TO_CHAR( date_value, 'YYYY-MM-DD' ) AS formatted_date FROM table_name;
Uscite:
FORMATTED_DATE
--------------
2000-01-01
2016-07-21
2016-07-21
E:
SELECT TO_CHAR(
date_value,
'FMMonth d yyyy, hh12:mi:ss AM',
'NLS_DATE_LANGUAGE = French'
) AS formatted_date
FROM table_name;
Uscite:
FORMATTED_DATE
-----------------------------
Janvier 01 2000, 12:00:00 AM
Juillet 21 2016, 08:00:00 AM
Juillet 21 2016, 19:08:31 PM
Impostazione del modello di formato data predefinito
Quando Oracle converte implicitamente da un DATE a una stringa o viceversa (o quando TO_CHAR() o TO_DATE() vengono chiamati esplicitamente senza un modello di formato) il parametro della sessione NLS_DATE_FORMAT verrà utilizzato come modello di formato nella conversione. Se il valore letterale non corrisponde al modello di formato, verrà sollevata un'eccezione.
È possibile rivedere questo parametro utilizzando:
SELECT VALUE FROM NLS_SESSION_PARAMETERS WHERE PARAMETER = 'NLS_DATE_FORMAT';
È possibile impostare questo valore all'interno della sessione corrente usando:
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
(Nota: questo non cambia il valore per nessun altro utente).
Se ti affidi a NLS_DATE_FORMAT per fornire la maschera di formato in TO_DATE() o TO_CHAR() non dovresti sorprenderti quando le query si interrompono se questo valore viene modificato.
Modifica delle date di visualizzazione degli sviluppatori SQL / Plus o SQL
Quando SQL / Plus o SQL Developer visualizzano le date, eseguiranno una conversione implicita in una stringa utilizzando il modello di formato data predefinito (vedere l'esempio Impostazione del modello di formato data predefinito).
È possibile modificare la modalità di visualizzazione di una data modificando il parametro NLS_DATE_FORMAT .
Data Aritmetica - Differenza tra le date in giorni, ore, minuti e / o secondi
In oracolo, la differenza (in giorni e / o frazioni) tra due DATE può essere trovata usando la sottrazione:
SELECT DATE '2016-03-23' - DATE '2015-12-25' AS difference FROM DUAL;
Emette il numero di giorni tra le due date:
DIFFERENCE
----------
89
E:
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
Emette la frazione di giorni tra due date:
DIFFERENCE
----------
1.0425
La differenza in ore, minuti o secondi può essere trovata moltiplicando questo numero rispettivamente di 24 , 24*60 o 24*60*60 .
L'esempio precedente può essere modificato per ottenere i giorni, le ore, i minuti e i secondi tra due date utilizzando:
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() viene utilizzato anziché FLOOR() per gestire correttamente le differenze negative).
Uscite:
DAYS HOURS MINUTES SECONDS
---- ----- ------- -------
1 1 1 12
L'esempio precedente può anche essere risolto convertendo la differenza numerica in un intervallo utilizzando 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
);
Data Aritmetica - Differenza tra le date in mesi o anni
La differenza di mesi tra due date può essere trovata utilizzando il MONTHS_BETWEEN( date1, date2 ) :
SELECT MONTHS_BETWEEN( DATE '2016-03-10', DATE '2015-03-10' ) AS difference FROM DUAL;
Uscite:
DIFFERENCE
----------
12
Se la differenza include mesi parte, restituirà la frazione del mese in base a 31 giorni in ciascun mese:
SELECT MONTHS_BETWEEN( DATE '2015-02-15', DATE '2015-01-01' ) AS difference FROM DUAL;
Uscite:
DIFFERENCE
----------
1.4516129
A causa di MONTHS_BETWEEN ipotizzando 31 giorni al mese in cui ci possono essere meno giorni al mese, questo può generare valori diversi per le differenze che si estendono tra i mesi.
Esempio:
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;
Produzione:
JAN-FEB FEB-MAR MAR-APR APR-MAY
------- ------- ------- -------
0.03226 0.09677 0.03226 0.06452
La differenza in anni può essere trovata dividendo la differenza del mese di 12.
Estrarre l'anno, mese, giorno, ora, minuti o secondi componenti di una data
I componenti di anno, mese o giorno di un tipo di dati DATE possono essere trovati utilizzando 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;
Uscite:
YEAR MONTH DAY
---- ----- ---
2016 7 25
I componenti del tempo (ore, minuti o secondi) possono essere trovati da entrambi:
- Utilizzando
CAST( datevalue AS TIMESTAMP )per convertire ilDATEin unTIMESTAMPe quindi usandoEXTRACT( [ HOUR | MINUTE | SECOND ] FROM timestampvalue ); o - Usare
TO_CHAR( datevalue, format_model )per ottenere il valore come stringa.
Per esempio:
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
);
Uscite:
HOURS MINUTES SECONDS
----- ------- -------
9 42 1
Fusi orari e ora legale
Il tipo di dati DATE non gestisce i fusi orari o le modifiche all'ora legale.
O:
- utilizzare il tipo di dati
TIMESTAMP WITH TIME ZONE; o - gestire le modifiche nella logica dell'applicazione.
Una DATE può essere archiviata come Coordinated Universal Time (UTC) e convertita nel fuso orario della sessione corrente in questo modo:
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;
Se esegui ALTER SESSION SET TIME_ZONE = '+01:00'; quindi l'output è:
TIME
------------------------------------
2016-01-01 13:00:00.000000000 +01:00
e ALTER SESSION SET TIME_ZONE = 'PST'; quindi l'output è:
TIME
------------------------------------
2016-01-01 04:00:00.000000000 PST
Leap Seconds
Oracle non gestisce i secondi bisestili . Per ulteriori dettagli, 2019397.2 nota My Oracle Support 2019397.2 e 730795.1 .
Ottenere il giorno della settimana
Puoi utilizzare TO_CHAR( date_value, 'D' ) per ottenere il giorno della settimana.
Tuttavia, questo dipende dal parametro della sessione NLS_TERRITORY :
ALTER SESSION SET NLS_TERRITORY = 'AMERICA'; -- First day of week is Sunday
SELECT TO_CHAR( DATE '1970-01-01', 'D' ) FROM DUAL;
Uscite 5
ALTER SESSION SET NLS_TERRITORY = 'UNITED KINGDOM'; -- First day of week is Monday
SELECT TO_CHAR( DATE '1970-01-01', 'D' ) FROM DUAL;
Uscite 4
Per fare ciò indipendentemente dalle impostazioni NLS , puoi troncare la data a mezzanotte del giorno corrente (per rimuovere eventuali frazioni di giorni) e sottrarre la data troncata all'inizio della iso-settimana corrente (che inizia sempre il lunedì):
SELECT TRUNC( date_value ) - TRUNC( date_value, 'IW' ) + 1 FROM DUAL
