Oracle Database
Daktyle
Szukaj…
Generowanie dat bez komponentu czasu
Wszystkie DATE mają składnik czasu; jednak zwyczajowo przechowuje się daty, które nie muszą zawierać informacji o czasie, a godziny / minuty / sekundy są ustawione na zero (tj. północ).
Użyj literału ANSI DATE (używając formatu daty ISO 8601 ):
SELECT DATE '2000-01-01' FROM DUAL;
Konwertuj go z literału ciągu za pomocą TO_DATE() :
SELECT TO_DATE( '2001-01-01', 'YYYY-MM-DD' ) FROM DUAL;
(Więcej informacji na temat modeli formatu daty można znaleźć w dokumentacji Oracle.)
lub:
SELECT TO_DATE(
'January 1, 2000, 00:00 A.M.',
'Month dd, YYYY, HH12:MI A.M.',
'NLS_DATE_LANGUAGE = American'
)
FROM DUAL;
(Jeśli konwertujesz terminy specyficzne dla języka, takie jak nazwy miesięcy, dobrą praktyką jest włączenie trzeciego parametru nlsparam do funkcji TO_DATE() i określenie języka, którego należy się spodziewać).
Generowanie dat za pomocą składnika czasu
Konwertuj go z literału ciągu za pomocą TO_DATE() :
SELECT TO_DATE( '2000-01-01 12:00:00', 'YYYY-MM-DD HH24:MI:SS' ) FROM DUAL;
Lub użyj literału TIMESTAMP :
CREATE TABLE date_table(
date_value DATE
);
INSERT INTO date_table ( date_value ) VALUES ( TIMESTAMP '2000-01-01 12:00:00' );
Oracle niejawnie rzuci TIMESTAMP na DATE podczas przechowywania go w kolumnie DATE tabeli; jednak możesz jawnie CAST() wartość na DATE :
SELECT CAST( TIMESTAMP '2000-01-01 12:00:00' AS DATE ) FROM DUAL;
Format daty
W Oracle typ danych DATE nie ma formatu; gdy Oracle wyśle DATE do programu klienckiego (SQL / Plus, SQL / Developer, Toad, Java, Python itp.), wyśle 7- lub 8-bajtowe reprezentujące datę.
DATE która nie jest przechowywana w tabeli (tj. Generowana przez SYSDATE i mająca „typ 13” przy użyciu polecenia DUMP() ) ma 8 bajtów i strukturę (liczby po prawej stronie są wewnętrzną reprezentacją 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
DATE przechowywana w tabeli („typ 12” przy użyciu polecenia DUMP() ) ma 7 bajtów i strukturę (liczby po prawej stronie są wewnętrzną reprezentacją 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
Jeśli chcesz, aby data miała określony format, musisz przekonwertować ją na coś, co ma format (np. Ciąg znaków). Klient SQL może to zrobić pośrednio lub można jawnie przekonwertować wartość na ciąg za pomocą TO_CHAR( date, format_model, nls_params ) .
Konwertowanie dat na ciąg
Użyj TO_CHAR( date [, format_model [, nls_params]] ) :
(Uwaga: jeśli nie podano modelu formatu, wówczas jako domyślny model formatu zostanie użyty parametr sesji NLS_DATE_FORMAT ; może być inny dla każdej sesji, więc nie należy na nim polegać. Dobrą praktyką jest zawsze określanie modelu formatu).
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 );
Następnie:
SELECT TO_CHAR( date_value, 'YYYY-MM-DD' ) AS formatted_date FROM table_name;
Wyjścia:
FORMATTED_DATE
--------------
2000-01-01
2016-07-21
2016-07-21
I:
SELECT TO_CHAR(
date_value,
'FMMonth d yyyy, hh12:mi:ss AM',
'NLS_DATE_LANGUAGE = French'
) AS formatted_date
FROM table_name;
Wyjścia:
FORMATTED_DATE
-----------------------------
Janvier 01 2000, 12:00:00 AM
Juillet 21 2016, 08:00:00 AM
Juillet 21 2016, 19:08:31 PM
Ustawianie domyślnego modelu formatu daty
Gdy Oracle niejawnie konwertuje z DATE na ciąg lub odwrotnie (lub gdy TO_CHAR() lub TO_DATE() są jawnie wywoływane bez modelu formatu), parametr sesji NLS_DATE_FORMAT zostanie użyty jako model formatu w konwersji. Jeśli literał nie pasuje do modelu formatu, zgłoszony zostanie wyjątek.
Możesz przejrzeć ten parametr za pomocą:
SELECT VALUE FROM NLS_SESSION_PARAMETERS WHERE PARAMETER = 'NLS_DATE_FORMAT';
Możesz ustawić tę wartość w ramach bieżącej sesji, używając:
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
(Uwaga: nie zmienia to wartości dla innych użytkowników).
Jeśli polegasz na NLS_DATE_FORMAT aby zapewnić maskę formatu w TO_DATE() lub TO_CHAR() , nie powinieneś być zaskoczony, gdy Twoje zapytania się zepsują, jeśli ta wartość zostanie kiedykolwiek zmieniona.
Zmiana sposobu wyświetlania dat przez SQL / Plus lub SQL Developer
Kiedy SQL / Plus lub SQL Developer wyświetlają daty, wykonają niejawną konwersję na ciąg przy użyciu domyślnego modelu formatu daty (patrz przykład Ustawianie domyślnego modelu formatu daty ).
Możesz zmienić sposób wyświetlania daty, zmieniając parametr NLS_DATE_FORMAT .
Arytmetyka daty - różnica między datami w dniach, godzinach, minutach i / lub sekundach
W wyroczni różnicę (w dniach i / lub ich ułamkach) między dwoma DATE można znaleźć, odejmując:
SELECT DATE '2016-03-23' - DATE '2015-12-25' AS difference FROM DUAL;
Zwraca liczbę dni między dwiema datami:
DIFFERENCE
----------
89
I:
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
Zwraca ułamek dni między dwiema datami:
DIFFERENCE
----------
1.0425
Różnicę w godzinach, minutach lub sekundach można znaleźć, mnożąc tę liczbę odpowiednio przez 24 , 24*60 lub 24*60*60 .
Poprzedni przykład można zmienić, aby uzyskać dni, godziny, minuty i sekundy między dwiema datami, używając:
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
);
(Uwaga: TRUNC() jest używane zamiast FLOOR() do prawidłowej obsługi różnic ujemnych).
Wyjścia:
DAYS HOURS MINUTES SECONDS
---- ----- ------- -------
1 1 1 12
Poprzedni przykład można również rozwiązać, przekształcając różnicę liczbową w interwał za pomocą 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
);
Arytmetyka daty - różnica między datami w miesiącach lub latach
Różnicę w miesiącach między dwiema datami można znaleźć za pomocą MONTHS_BETWEEN( date1, date2 ) :
SELECT MONTHS_BETWEEN( DATE '2016-03-10', DATE '2015-03-10' ) AS difference FROM DUAL;
Wyjścia:
DIFFERENCE
----------
12
Jeśli różnica obejmuje część miesięcy, zwróci ułamek miesiąca na podstawie 31 dni w każdym miesiącu:
SELECT MONTHS_BETWEEN( DATE '2015-02-15', DATE '2015-01-01' ) AS difference FROM DUAL;
Wyjścia:
DIFFERENCE
----------
1.4516129
Ponieważ MONTHS_BETWEEN zakłada 31 dni w miesiącu, kiedy może być mniej dni w miesiącu, może to skutkować różnymi wartościami różnic obejmujących granice między miesiącami.
Przykład:
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;
Wynik:
JAN-FEB FEB-MAR MAR-APR APR-MAY
------- ------- ------- -------
0.03226 0.09677 0.03226 0.06452
Różnicę w latach można znaleźć, dzieląc różnicę miesiąca przez 12.
Wyodrębnij rok, miesiąc, dzień, godzinę, minutę lub drugi składnik daty
Składniki roku, miesiąca lub dnia typu danych DATE można znaleźć za pomocą 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;
Wyjścia:
YEAR MONTH DAY
---- ----- ---
2016 7 25
Składniki czasu (godziny, minuty lub sekundy) można znaleźć:
- Użycie
CAST( datevalue AS TIMESTAMP )do konwersjiDATEnaTIMESTAMPa następnie użycieEXTRACT( [ HOUR | MINUTE | SECOND ] FROM timestampvalue ); lub - Użycie
TO_CHAR( datevalue, format_model )aby uzyskać wartość jako ciąg.
Na przykład:
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
);
Wyjścia:
HOURS MINUTES SECONDS
----- ------- -------
9 42 1
Strefy czasowe i czas letni
Typ danych DATE nie obsługuje stref czasowych ani zmian czasu letniego.
Zarówno:
- użyj
TIMESTAMP WITH TIME ZONEdanychTIMESTAMP WITH TIME ZONE; lub - obsłużyć zmiany w logice aplikacji.
DATE można zapisać jako uniwersalny czas koordynowany (UTC) i przekonwertować na bieżącą strefę czasową sesji w następujący sposób:
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;
Jeśli uruchomisz ALTER SESSION SET TIME_ZONE = '+01:00'; następnie dane wyjściowe to:
TIME
------------------------------------
2016-01-01 13:00:00.000000000 +01:00
i ALTER SESSION SET TIME_ZONE = 'PST'; następnie dane wyjściowe to:
TIME
------------------------------------
2016-01-01 04:00:00.000000000 PST
Skokowe sekundy
Oracle nie obsługuje sekund przestępnych . Więcej informacji 2019397.2 notatce My Oracle Support 2019397.2 i 730795.1 .
Pierwsze dzień tygodnia
Możesz użyć TO_CHAR( date_value, 'D' ) aby uzyskać dzień tygodnia.
Jest to jednak zależne od parametru sesji NLS_TERRITORY :
ALTER SESSION SET NLS_TERRITORY = 'AMERICA'; -- First day of week is Sunday
SELECT TO_CHAR( DATE '1970-01-01', 'D' ) FROM DUAL;
Wyjścia 5
ALTER SESSION SET NLS_TERRITORY = 'UNITED KINGDOM'; -- First day of week is Monday
SELECT TO_CHAR( DATE '1970-01-01', 'D' ) FROM DUAL;
Wyjścia 4
Aby to zrobić niezależnie od ustawień NLS , możesz skrócić datę do północy bieżącego dnia (aby usunąć ułamki dni) i odjąć datę obciętą do początku bieżącego izo-tygodnia (który zawsze zaczyna się w poniedziałek):
SELECT TRUNC( date_value ) - TRUNC( date_value, 'IW' ) + 1 FROM DUAL
