Oracle Database
datum
Sök…
Genererar datum med ingen tidskomponent
Alla DATE har en tidskomponent; emellertid är det vanligt att lagra datum som inte behöver inkludera tidsinformation med timmar / minuter / sekunder inställda på noll (dvs. midnatt).
Använd en ANSI DATE bokstav (med ISO 8601-datumformat ):
SELECT DATE '2000-01-01' FROM DUAL;
Konvertera den från en TO_DATE() med TO_DATE() :
SELECT TO_DATE( '2001-01-01', 'YYYY-MM-DD' ) FROM DUAL;
(Mer information om datumformatmodeller finns i Oracle-dokumentationen.)
eller:
SELECT TO_DATE(
'January 1, 2000, 00:00 A.M.',
'Month dd, YYYY, HH12:MI A.M.',
'NLS_DATE_LANGUAGE = American'
)
FROM DUAL;
(Om du konverterar språkspecifika termer som månadsnamn är det bra att inkludera den tredje nlsparam parametern till TO_DATE() -funktionen och ange vilket språk som ska förväntas.)
Generera datum med en tidskomponent
Konvertera den från en TO_DATE() med TO_DATE() :
SELECT TO_DATE( '2000-01-01 12:00:00', 'YYYY-MM-DD HH24:MI:SS' ) FROM DUAL;
Eller använd en TIMESTAMP bokstav :
CREATE TABLE date_table(
date_value DATE
);
INSERT INTO date_table ( date_value ) VALUES ( TIMESTAMP '2000-01-01 12:00:00' );
Oracle kastar implicit en TIMESTAMP till en DATE när den lagras i en DATE kolumn i en tabell; Du kan dock uttryckligen CAST() värdet till en DATE :
SELECT CAST( TIMESTAMP '2000-01-01 12:00:00' AS DATE ) FROM DUAL;
Datum för format
I Oracle har en DATE datatyp inget format; när Oracle skickar en DATE till klientprogrammet (SQL / Plus, SQL / Developer, padda, Java, Python, etc.) kommer det att skickas 7- eller 8- byte som representerar datumet.
En DATE som inte lagras i en tabell (dvs. genereras av SYSDATE och har "typ 13" när du använder DUMP() ) har 8-byte och har strukturen (siffrorna till höger är den interna representationen för 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
En DATE som lagras i en tabell ("typ 12" när du använder DUMP() ) har 7-byte och har strukturen (siffrorna till höger är den interna representationen för 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
Om du vill att datumet ska ha ett specifikt format måste du konvertera det till något som har ett format (dvs. en sträng). SQL-klienten kan implicit göra detta eller så kan du uttryckligen konvertera värdet till en sträng med TO_CHAR( date, format_model, nls_params ) .
Konvertera datum till en sträng
Använd TO_CHAR( date [, format_model [, nls_params]] ) :
(Observera: om en formatmodell inte tillhandahålls så används NLS_DATE_FORMAT sessionsparametern som standardformatmodell . Det kan vara annorlunda för varje session så bör inte lita på. Det är bra att alltid ange formatmodellen.)
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 );
Sedan:
SELECT TO_CHAR( date_value, 'YYYY-MM-DD' ) AS formatted_date FROM table_name;
utgångar:
FORMATTED_DATE
--------------
2000-01-01
2016-07-21
2016-07-21
Och:
SELECT TO_CHAR(
date_value,
'FMMonth d yyyy, hh12:mi:ss AM',
'NLS_DATE_LANGUAGE = French'
) AS formatted_date
FROM table_name;
utgångar:
FORMATTED_DATE
-----------------------------
Janvier 01 2000, 12:00:00 AM
Juillet 21 2016, 08:00:00 AM
Juillet 21 2016, 19:08:31 PM
Ställa in standardmodellen för datumformat
När Oracle underförstått konverterar från en DATE till en sträng eller vice versa (eller när TO_CHAR() eller TO_DATE() kallas uttryckligen utan en NLS_DATE_FORMAT ) kommer NLS_DATE_FORMAT att användas som formatmodell i konverteringen. Om det bokstavliga inte stämmer med formatmodellen kommer ett undantag att tas upp.
Du kan granska denna parameter med:
SELECT VALUE FROM NLS_SESSION_PARAMETERS WHERE PARAMETER = 'NLS_DATE_FORMAT';
Du kan ställa in detta värde inom din nuvarande session med:
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
(Obs: detta ändrar inte värdet för andra användare.)
Om du litar på NLS_DATE_FORMAT att tillhandahålla TO_DATE() i TO_DATE() eller TO_CHAR() bör du inte bli förvånad när dina frågor bryts om detta värde någonsin ändras.
Ändra hur SQL / Plus eller SQL Developer visar datum
När SQL / Plus eller SQL Developer visar datum kommer de att utföra en implicit konvertering till en sträng med standarddatumformatmodellen (se Ställa in standarddatamodellmodellen ).
Du kan ändra hur ett datum visas genom att ändra NLS_DATE_FORMAT parametern.
Datum aritmetik - Skillnad mellan datum i dagar, timmar, minuter och / eller sekunder
I oracle kan skillnaden (i dagar och / eller fraktioner därav) mellan två DATE hittas med hjälp av subtraktion:
SELECT DATE '2016-03-23' - DATE '2015-12-25' AS difference FROM DUAL;
Visar antalet dagar mellan de två datumen:
DIFFERENCE
----------
89
Och:
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
Visar fraktionen av dagar mellan två datum:
DIFFERENCE
----------
1.0425
Skillnaden i timmar, minuter eller sekunder kan hittas genom att multiplicera detta nummer med 24 , 24*60 respektive 24*60*60 .
Det föregående exemplet kan ändras för att få dagar, timmar, minuter och sekunder mellan två datum med hjälp av:
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
);
(Obs: TRUNC() används snarare än FLOOR() att hantera negativa skillnader korrekt.)
utgångar:
DAYS HOURS MINUTES SECONDS
---- ----- ------- -------
1 1 1 12
Det föregående exemplet kan också lösas genom att konvertera den numeriska skillnaden till ett intervall med 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
);
Datum aritmetik - Skillnad mellan datum i månader eller år
Skillnaden i månader mellan två datum kan hittas med MONTHS_BETWEEN( date1, date2 ) :
SELECT MONTHS_BETWEEN( DATE '2016-03-10', DATE '2015-03-10' ) AS difference FROM DUAL;
utgångar:
DIFFERENCE
----------
12
Om skillnaden inkluderar delmånader returnerar den bråkdelen av månaden baserat på att det finns 31 dagar i varje månad:
SELECT MONTHS_BETWEEN( DATE '2015-02-15', DATE '2015-01-01' ) AS difference FROM DUAL;
utgångar:
DIFFERENCE
----------
1.4516129
På grund av att MONTHS_BETWEEN antar 31 dagar per månad när det kan vara färre dagar per månad kan detta resultera i olika värden för skillnader som sträcker sig över gränserna mellan månader.
Exempel:
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;
Produktion:
JAN-FEB FEB-MAR MAR-APR APR-MAY
------- ------- ------- -------
0.03226 0.09677 0.03226 0.06452
Skillnaden i år kan hittas genom att månadsskillnaden delas med 12.
Extrahera år, månad, dag, timme, minut eller andra delar av ett datum
Året, månaden eller DATE av en DATE datatyp kan hittas med hjälp av 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;
utgångar:
YEAR MONTH DAY
---- ----- ---
2016 7 25
Tiden (timme, minut eller sekund) komponenter kan hittas av antingen:
- Använda
CAST( datevalue AS TIMESTAMP )att konverteraDATEtill ettTIMESTAMPoch sedan användaEXTRACT( [ HOUR | MINUTE | SECOND ] FROM timestampvalue ); eller - Använd
TO_CHAR( datevalue, format_model )att få värdet som en sträng.
Till exempel:
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
);
utgångar:
HOURS MINUTES SECONDS
----- ------- -------
9 42 1
Tidzoner och sommartid
DATE hanterar inte tidszoner eller förändringar i sommartid.
Antingen:
- använd
TIMESTAMP WITH TIME ZONE; eller - hantera ändringarna i din applikationslogik.
En DATE kan lagras som samordnad universaltid (UTC) och konverteras till den aktuella sessionstidszonen så här:
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;
Om du kör ALTER SESSION SET TIME_ZONE = '+01:00'; då är utgången:
TIME
------------------------------------
2016-01-01 13:00:00.000000000 +01:00
och ALTER SESSION SET TIME_ZONE = 'PST'; då är utgången:
TIME
------------------------------------
2016-01-01 04:00:00.000000000 PST
Hoppa sekunder
Oracle hanterar inte språngsekunder . Se My Oracle Support-not 2019397.2 och 730795.1 för mer information.
Få veckans dag
Du kan använda TO_CHAR( date_value, 'D' ) att få veckodag.
Detta är dock beroende av NLS_TERRITORY sessionsparametern:
ALTER SESSION SET NLS_TERRITORY = 'AMERICA'; -- First day of week is Sunday
SELECT TO_CHAR( DATE '1970-01-01', 'D' ) FROM DUAL;
Utgångar 5
ALTER SESSION SET NLS_TERRITORY = 'UNITED KINGDOM'; -- First day of week is Monday
SELECT TO_CHAR( DATE '1970-01-01', 'D' ) FROM DUAL;
Utgångar 4
För att göra detta oberoende av NLS inställningarna kan du stänga in datumet till midnatt på den aktuella dagen (för att ta bort eventuella fraktioner av dagar) och subtrahera det datum som trunkeras till början av den aktuella iso-veckan (som alltid börjar på måndag):
SELECT TRUNC( date_value ) - TRUNC( date_value, 'IW' ) + 1 FROM DUAL