Oracle Database
data
Zoeken…
Datums genereren zonder tijdcomponent
Alle DATE s hebben een tijdcomponent; Het is echter gebruikelijk om datums op te slaan die geen tijdinformatie hoeven te bevatten met de uren / minuten / seconden ingesteld op nul (bijv. middernacht).
Gebruik een letterlijke ANSI- DATE (met ISO 8601-datumnotatie ):
SELECT DATE '2000-01-01' FROM DUAL;
Converteer het van een letterlijke tekenreeks met TO_DATE() :
SELECT TO_DATE( '2001-01-01', 'YYYY-MM-DD' ) FROM DUAL;
(Meer informatie over de datumnotatiemodellen is te vinden in de Oracle-documentatie.)
of:
SELECT TO_DATE(
'January 1, 2000, 00:00 A.M.',
'Month dd, YYYY, HH12:MI A.M.',
'NLS_DATE_LANGUAGE = American'
)
FROM DUAL;
(Als u taalspecifieke termen zoals nlsparam converteert, is het een goede gewoonte om de 3e parameter nlsparam te nemen in de functie nlsparam TO_DATE() en de te verwachten taal op te geven.)
Datums genereren met een tijdcomponent
Converteer het van een letterlijke tekenreeks met TO_DATE() :
SELECT TO_DATE( '2000-01-01 12:00:00', 'YYYY-MM-DD HH24:MI:SS' ) FROM DUAL;
Of gebruik een letterlijke TIMESTAMP :
CREATE TABLE date_table(
date_value DATE
);
INSERT INTO date_table ( date_value ) VALUES ( TIMESTAMP '2000-01-01 12:00:00' );
Oracle zal impliciet een TIMESTAMP naar een DATE TIMESTAMP wanneer deze in een DATE kolom van een tabel wordt opgeslagen; U kunt de waarde echter expliciet CAST() tot een DATE :
SELECT CAST( TIMESTAMP '2000-01-01 12:00:00' AS DATE ) FROM DUAL;
De notatie van een datum
In Oracle heeft een gegevenstype DATE geen indeling; wanneer Oracle een DATE verzendt naar het clientprogramma (SQL / Plus, SQL / Developer, Toad, Java, Python, enz.), verzendt het 7- of 8-bytes die de datum vertegenwoordigen.
Een DATE die niet is opgeslagen in een tabel (dat wil zeggen gegenereerd door SYSDATE en met "type 13" bij gebruik van de opdracht DUMP() ) heeft 8 bytes en heeft de structuur (de getallen aan de rechterkant zijn de interne weergave van 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
Een DATE die is opgeslagen in een tabel ("type 12" bij gebruik van de opdracht DUMP() ) heeft 7 bytes en heeft de structuur (de getallen aan de rechterkant zijn de interne weergave van 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
Als u wilt dat de datum een specifieke notatie heeft, moet u deze converteren naar iets met een notatie (bijvoorbeeld een tekenreeks). De SQL-client kan dit impliciet doen of u kunt de waarde expliciet omzetten in een tekenreeks met TO_CHAR( date, format_model, nls_params ) .
Datums omzetten in een tekenreeks
Gebruik TO_CHAR( date [, format_model [, nls_params]] ) :
(Opmerking: als er geen indelingsmodel wordt opgegeven, wordt de NLS_DATE_FORMAT sessieparameter gebruikt als het standaard indelingsmodel ; dit kan voor elke sessie verschillen, dus u kunt er niet op vertrouwen. Het is een goede gewoonte om altijd het indelingsmodel op te geven.)
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 );
Vervolgens:
SELECT TO_CHAR( date_value, 'YYYY-MM-DD' ) AS formatted_date FROM table_name;
uitgangen:
FORMATTED_DATE
--------------
2000-01-01
2016-07-21
2016-07-21
En:
SELECT TO_CHAR(
date_value,
'FMMonth d yyyy, hh12:mi:ss AM',
'NLS_DATE_LANGUAGE = French'
) AS formatted_date
FROM table_name;
uitgangen:
FORMATTED_DATE
-----------------------------
Janvier 01 2000, 12:00:00 AM
Juillet 21 2016, 08:00:00 AM
Juillet 21 2016, 19:08:31 PM
Het standaard datumformaatmodel instellen
Wanneer Oracle impliciet converteert van een DATE naar een tekenreeks of omgekeerd (of wanneer TO_CHAR() of TO_DATE() expliciet zonder een indelingsmodel worden aangeroepen), wordt de parameter NLS_DATE_FORMAT sessie gebruikt als het indelingsmodel in de conversie. Als het letterlijke niet overeenkomt met het formaatmodel, wordt er een uitzondering gemaakt.
U kunt deze parameter bekijken met:
SELECT VALUE FROM NLS_SESSION_PARAMETERS WHERE PARAMETER = 'NLS_DATE_FORMAT';
U kunt deze waarde instellen in uw huidige sessie met:
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
(Opmerking: dit verandert niets aan de waarde voor andere gebruikers.)
Als u op NLS_DATE_FORMAT vertrouwt om het NLS_DATE_FORMAT in TO_DATE() of TO_CHAR() moet u niet verbaasd zijn wanneer uw query's worden afgebroken als deze waarde ooit wordt gewijzigd.
Wijzigen hoe SQL / Plus of SQL Developer datums weergeven
Wanneer SQL / Plus of SQL Developer datums weergeven, voeren ze een impliciete conversie uit naar een tekenreeks met behulp van het standaard datumnotatiemodel (zie het voorbeeld van het standaard datumnotatiemodel instellen ).
U kunt wijzigen hoe een datum wordt weergegeven door de parameter NLS_DATE_FORMAT wijzigen.
Rekenkundige datum - Verschil tussen datums in dagen, uren, minuten en / of seconden
In orakel kan het verschil (in dagen en / of fracties daarvan) tussen twee DATE s worden gevonden met behulp van aftrekking:
SELECT DATE '2016-03-23' - DATE '2015-12-25' AS difference FROM DUAL;
Voert het aantal dagen tussen de twee datums uit:
DIFFERENCE
----------
89
En:
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
Voert de fractie dagen tussen twee datums uit:
DIFFERENCE
----------
1.0425
Het verschil in uren, minuten of seconden kan worden gevonden door dit aantal te vermenigvuldigen met respectievelijk 24 , 24*60 of 24*60*60 .
Het vorige voorbeeld kan worden gewijzigd om de dagen, uren, minuten en seconden tussen twee datums te krijgen met:
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
);
(Opmerking: TRUNC() wordt gebruikt in plaats van FLOOR() om negatieve verschillen correct af te handelen.)
uitgangen:
DAYS HOURS MINUTES SECONDS
---- ----- ------- -------
1 1 1 12
Het vorige voorbeeld kan ook worden opgelost door het numerieke verschil te converteren naar een interval met 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
);
Rekenkundige datum - Verschil tussen datums in maanden of jaren
Het verschil in maanden tussen twee datums kan worden gevonden met MONTHS_BETWEEN( date1, date2 ) :
SELECT MONTHS_BETWEEN( DATE '2016-03-10', DATE '2015-03-10' ) AS difference FROM DUAL;
uitgangen:
DIFFERENCE
----------
12
Als het verschil deelmaanden bevat, retourneert het de fractie van de maand op basis van 31 dagen in elke maand:
SELECT MONTHS_BETWEEN( DATE '2015-02-15', DATE '2015-01-01' ) AS difference FROM DUAL;
uitgangen:
DIFFERENCE
----------
1.4516129
Omdat MONTHS_BETWEEN 31 dagen per maand veronderstelt dat er minder dagen per maand kunnen zijn, kan dit resulteren in verschillende waarden voor verschillen die de grenzen tussen maanden overschrijden.
Voorbeeld:
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;
Output:
JAN-FEB FEB-MAR MAR-APR APR-MAY
------- ------- ------- -------
0.03226 0.09677 0.03226 0.06452
Het verschil in jaren kan worden gevonden door het maandverschil te delen door 12.
Extraheer de jaar-, maand-, dag-, uur-, minuut- of tweede componenten van een datum
De jaar-, EXTRACT( [ YEAR | MONTH | DAY ] FROM datevalue ) van een gegevenstype DATE kunnen worden gevonden met behulp van de 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;
uitgangen:
YEAR MONTH DAY
---- ----- ---
2016 7 25
De tijdcomponenten (uur, minuut of seconde) kunnen worden gevonden door:
- Met behulp van
CAST( datevalue AS TIMESTAMP )om deDATEte converteren naar eenTIMESTAMPen vervolgensEXTRACT( [ HOUR | MINUTE | SECOND ] FROM timestampvalue ); of -
TO_CHAR( datevalue, format_model )gebruiken om de waarde als een tekenreeks op te halen.
Bijvoorbeeld:
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
);
uitgangen:
HOURS MINUTES SECONDS
----- ------- -------
9 42 1
Tijdzones en zomertijd
Het gegevenstype DATE verwerkt geen tijdzones of veranderingen in de zomertijd.
Een van beide:
- gebruik het gegevenstype
TIMESTAMP WITH TIME ZONE; of - omgaan met de wijzigingen in uw applicatielogica.
Een DATE kan worden opgeslagen als Coordinated Universal Time (UTC) en als volgt worden geconverteerd naar de huidige sessietijdzone:
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;
Als u ALTER SESSION SET TIME_ZONE = '+01:00'; dan is de output:
TIME
------------------------------------
2016-01-01 13:00:00.000000000 +01:00
en ALTER SESSION SET TIME_ZONE = 'PST'; dan is de output:
TIME
------------------------------------
2016-01-01 04:00:00.000000000 PST
Sprongseconden
Oracle verwerkt geen schrikkelseconden . Zie Mijn Oracle Support note 2019397.2 en 730795.1 voor meer informatie.
De dag van de week krijgen
U kunt TO_CHAR( date_value, 'D' ) gebruiken om de dag van de week te krijgen.
Dit is echter afhankelijk van de NLS_TERRITORY :
ALTER SESSION SET NLS_TERRITORY = 'AMERICA'; -- First day of week is Sunday
SELECT TO_CHAR( DATE '1970-01-01', 'D' ) FROM DUAL;
Uitgangen 5
ALTER SESSION SET NLS_TERRITORY = 'UNITED KINGDOM'; -- First day of week is Monday
SELECT TO_CHAR( DATE '1970-01-01', 'D' ) FROM DUAL;
Uitgangen 4
Om dit onafhankelijk van de NLS instellingen te doen, kunt u de datum inkorten tot middernacht van de huidige dag (om fracties van dagen te verwijderen) en de ingekorte datum aftrekken van het begin van de huidige iso-week (die altijd op maandag begint):
SELECT TRUNC( date_value ) - TRUNC( date_value, 'IW' ) + 1 FROM DUAL
