Oracle Database
Termine
Suche…
Daten ohne Zeitkomponente generieren
Alle DATE 's haben eine Zeitkomponente; Es ist jedoch üblich, Datumsangaben zu speichern, die keine Zeitangaben enthalten müssen, wobei die Stunden / Minuten / Sekunden auf Null (dh Mitternacht) gesetzt sind.
Verwenden Sie ein ANSI DATE Literal (unter Verwendung des Datumsformats nach ISO 8601 ):
SELECT DATE '2000-01-01' FROM DUAL;
Konvertieren Sie es mit TO_DATE() aus einem String-Literal:
SELECT TO_DATE( '2001-01-01', 'YYYY-MM-DD' ) FROM DUAL;
(Weitere Informationen zu den Datumsformatmodellen finden Sie in der Oracle-Dokumentation.)
oder:
SELECT TO_DATE(
'January 1, 2000, 00:00 A.M.',
'Month dd, YYYY, HH12:MI A.M.',
'NLS_DATE_LANGUAGE = American'
)
FROM DUAL;
(Wenn Sie sprachspezifische Begriffe wie Monatsnamen nlsparam , TO_DATE() es sich, den 3. nlsparam Parameter in die TO_DATE() Funktion aufzunehmen und die zu erwartende Sprache anzugeben.)
Daten mit einer Zeitkomponente generieren
Konvertieren Sie es mit TO_DATE() aus einem String-Literal:
SELECT TO_DATE( '2000-01-01 12:00:00', 'YYYY-MM-DD HH24:MI:SS' ) FROM DUAL;
Oder verwenden Sie ein TIMESTAMP Literal :
CREATE TABLE date_table(
date_value DATE
);
INSERT INTO date_table ( date_value ) VALUES ( TIMESTAMP '2000-01-01 12:00:00' );
Oracle wird ein TIMESTAMP implizit in ein DATE TIMESTAMP , wenn es in einer DATE Spalte einer Tabelle gespeichert wird. Sie können den Wert jedoch explizit zu einem DATE CAST() :
SELECT CAST( TIMESTAMP '2000-01-01 12:00:00' AS DATE ) FROM DUAL;
Das Format eines Datums
In Oracle hat ein DATE Datentyp kein Format. Wenn Oracle ein DATE an das Client-Programm sendet (SQL / Plus, SQL / Developer, Toad, Java, Python usw.), sendet es 7 oder 8 Byte, die das Datum darstellen.
Ein DATE das nicht in einer Tabelle gespeichert ist (dh von SYSDATE erzeugt wurde und bei Verwendung des DUMP() "Typ 13" hat), hat 8 Byte und hat die Struktur (die Zahlen auf der rechten Seite sind die interne Darstellung von 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
Ein DATE das in einer Tabelle gespeichert ist ("Typ 12" bei Verwendung des DUMP() ), hat 7 Bytes und die Struktur (die Zahlen auf der rechten Seite sind die interne Darstellung von 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
Wenn Sie möchten, dass das Datum ein bestimmtes Format hat, müssen Sie es in ein Format konvertieren, das ein Format hat (z. B. eine Zeichenfolge). Der SQL-Client kann dies implizit tun, oder Sie können den Wert mithilfe von TO_CHAR( date, format_model, nls_params ) explizit in eine Zeichenfolge TO_CHAR( date, format_model, nls_params ) .
Datumsangaben in einen String umwandeln
Verwenden Sie TO_CHAR( date [, format_model [, nls_params]] ) :
(Hinweis: Wenn ein Format - NLS_DATE_FORMAT Modell nicht zur Verfügung gestellt wird , wird die NLS_DATE_FORMAT Session - Parameter werden als verwendet werden Standardformat - Modell , das für jede Sitzung unterschiedlich sein , so kann nicht geltend gemacht werden , sollte es gute Praxis ist das Format - Modell immer angeben..)
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 );
Dann:
SELECT TO_CHAR( date_value, 'YYYY-MM-DD' ) AS formatted_date FROM table_name;
Ausgänge:
FORMATTED_DATE
--------------
2000-01-01
2016-07-21
2016-07-21
Und:
SELECT TO_CHAR(
date_value,
'FMMonth d yyyy, hh12:mi:ss AM',
'NLS_DATE_LANGUAGE = French'
) AS formatted_date
FROM table_name;
Ausgänge:
FORMATTED_DATE
-----------------------------
Janvier 01 2000, 12:00:00 AM
Juillet 21 2016, 08:00:00 AM
Juillet 21 2016, 19:08:31 PM
Festlegen des Standard-Datumsformatmodells
Wenn Oracle implizit von einem DATE in einen String oder umgekehrt konvertiert (oder wenn TO_CHAR() oder TO_DATE() explizit ohne TO_DATE() aufgerufen wird), wird der Sitzungsparameter NLS_DATE_FORMAT als NLS_DATE_FORMAT bei der Konvertierung verwendet. Wenn das Literal nicht mit dem Formatmodell übereinstimmt, wird eine Ausnahme ausgelöst.
Sie können diesen Parameter folgendermaßen überprüfen:
SELECT VALUE FROM NLS_SESSION_PARAMETERS WHERE PARAMETER = 'NLS_DATE_FORMAT';
Sie können diesen Wert in Ihrer aktuellen Sitzung einstellen, indem Sie
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
(Hinweis: Der Wert für andere Benutzer wird dadurch nicht geändert.)
Wenn Sie sich darauf NLS_DATE_FORMAT , dass NLS_DATE_FORMAT die Formatmaske in TO_DATE() oder TO_CHAR() , sollten Sie sich nicht wundern, wenn Ihre Abfragen brechen, wenn dieser Wert jemals geändert wird.
Ändern der Anzeigedaten von SQL / Plus oder SQL Developer
Wenn Datumsangaben von SQL / Plus oder SQL Developer angezeigt werden, wird eine implizite Konvertierung in eine Zeichenfolge mit dem Standardmodell für das Datumsformat durchgeführt (siehe Beispiel für das Standardformat für das Datumsformat ).
Sie können die Anzeige eines Datums ändern, indem Sie den Parameter NLS_DATE_FORMAT .
Datumsarithmetik - Differenz zwischen Datumsangaben in Tagen, Stunden, Minuten und / oder Sekunden
In Orakel kann die Differenz (in Tagen und / oder Brüchen davon) zwischen zwei DATE durch Subtraktion ermittelt werden:
SELECT DATE '2016-03-23' - DATE '2015-12-25' AS difference FROM DUAL;
Gibt die Anzahl der Tage zwischen den beiden Datumsangaben aus:
DIFFERENCE
----------
89
Und:
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
Gibt den Bruchteil der Tage zwischen zwei Datumsangaben aus:
DIFFERENCE
----------
1.0425
Die Differenz in Stunden, Minuten oder Sekunden kann ermittelt werden, indem diese Zahl mit 24 , 24*60 bzw. 24*60*60 multipliziert wird.
Das vorherige Beispiel kann geändert werden, um die Tage, Stunden, Minuten und Sekunden zwischen zwei Datumsangaben abzurufen.
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
);
(Hinweis: TRUNC() wird anstelle von FLOOR() , um negative Unterschiede korrekt zu behandeln.)
Ausgänge:
DAYS HOURS MINUTES SECONDS
---- ----- ------- -------
1 1 1 12
Das vorige Beispiel kann auch gelöst werden, indem die numerische Differenz mit NUMTODSINTERVAL() in ein Intervall 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
);
Datumsarithmetik - Differenz zwischen Datumsangaben in Monaten oder Jahren
Die Differenz in Monaten zwischen zwei Datumsangaben kann mit MONTHS_BETWEEN( date1, date2 ) :
SELECT MONTHS_BETWEEN( DATE '2016-03-10', DATE '2015-03-10' ) AS difference FROM DUAL;
Ausgänge:
DIFFERENCE
----------
12
Wenn die Differenz Teilmonate umfasst, wird der Bruchteil des Monats zurückgegeben, der auf 31 Tagen pro Monat basiert:
SELECT MONTHS_BETWEEN( DATE '2015-02-15', DATE '2015-01-01' ) AS difference FROM DUAL;
Ausgänge:
DIFFERENCE
----------
1.4516129
Da MONTHS_BETWEEN 31 Tage pro Monat MONTHS_BETWEEN , wenn es weniger Tage pro Monat gibt, kann dies zu unterschiedlichen Werten für Unterschiede führen, die die Grenzen zwischen Monaten überspannen.
Beispiel:
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;
Ausgabe:
JAN-FEB FEB-MAR MAR-APR APR-MAY
------- ------- ------- -------
0.03226 0.09677 0.03226 0.06452
Die Differenz in Jahren kann ermittelt werden, indem die Monatsdifferenz durch 12 geteilt wird.
Extrahieren Sie die Komponenten Jahr, Monat, Tag, Stunde, Minute oder Sekunde eines Datums
Die EXTRACT( [ YEAR | MONTH | DAY ] FROM datevalue ) , Monats- oder EXTRACT( [ YEAR | MONTH | DAY ] FROM datevalue ) eines DATE Datentyps können mit 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;
Ausgänge:
YEAR MONTH DAY
---- ----- ---
2016 7 25
Die Zeitkomponenten (Stunden, Minuten oder Sekunden) können gefunden werden durch:
- Verwenden Sie
CAST( datevalue AS TIMESTAMP ), um dasDATEin einTIMESTAMPund verwenden Sie dannEXTRACT( [ HOUR | MINUTE | SECOND ] FROM timestampvalue ). oder - Verwenden von
TO_CHAR( datevalue, format_model ), um den Wert als ZeichenfolgeTO_CHAR( datevalue, format_model ).
Zum Beispiel:
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
);
Ausgänge:
HOURS MINUTES SECONDS
----- ------- -------
9 42 1
Zeitzonen und Sommerzeit
Der Datentyp DATE verarbeitet keine Zeitzonen oder Änderungen in der Sommerzeit.
Entweder:
- Verwenden Sie den
TIMESTAMP WITH TIME ZONE. oder - handhaben Sie die Änderungen in Ihrer Anwendungslogik.
Ein DATE kann als koordinierte Weltzeit (UTC) gespeichert und wie folgt in die aktuelle Sitzungszeitzone konvertiert werden:
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;
Wenn Sie ALTER SESSION SET TIME_ZONE = '+01:00'; dann ist die Ausgabe:
TIME
------------------------------------
2016-01-01 13:00:00.000000000 +01:00
und ALTER SESSION SET TIME_ZONE = 'PST'; dann ist die Ausgabe:
TIME
------------------------------------
2016-01-01 04:00:00.000000000 PST
Schaltsekunden
Oracle verarbeitet Schaltsekunden nicht . Weitere 2019397.2 Sie in meinem Oracle-Support-Hinweis 2019397.2 und 730795.1 .
Den Tag der Woche bekommen
Sie können TO_CHAR( date_value, 'D' ) , um den Wochentag zu erhalten.
Dies ist jedoch vom Sitzungsparameter NLS_TERRITORY abhängig:
ALTER SESSION SET NLS_TERRITORY = 'AMERICA'; -- First day of week is Sunday
SELECT TO_CHAR( DATE '1970-01-01', 'D' ) FROM DUAL;
Ausgänge 5
ALTER SESSION SET NLS_TERRITORY = 'UNITED KINGDOM'; -- First day of week is Monday
SELECT TO_CHAR( DATE '1970-01-01', 'D' ) FROM DUAL;
Ausgänge 4
Um dies unabhängig von den NLS Einstellungen zu tun, können Sie das Datum auf Mitternacht des aktuellen Tages abschneiden (um etwaige Bruchteile von Tagen zu entfernen) und das abgeschnittene Datum vom Beginn der aktuellen Iso-Woche (die immer am Montag beginnt) abziehen:
SELECT TRUNC( date_value ) - TRUNC( date_value, 'IW' ) + 1 FROM DUAL