Oracle Database
Даты
Поиск…
Создание дат без компонента времени
Все DATE имеют временной компонент; однако обычно принято хранить даты, которые не обязательно должны включать информацию о времени с часами / минутами / секундами, установленными на ноль (т.е. полночь).
Используйте литерал ANSI DATE (с использованием формата даты ISO 8601 ):
SELECT DATE '2000-01-01' FROM DUAL;
Преобразуйте его из строкового литерала с помощью TO_DATE() :
SELECT TO_DATE( '2001-01-01', 'YYYY-MM-DD' ) FROM DUAL;
(Более подробную информацию о моделях формата даты можно найти в документации Oracle.)
или же:
SELECT TO_DATE(
'January 1, 2000, 00:00 A.M.',
'Month dd, YYYY, HH12:MI A.M.',
'NLS_DATE_LANGUAGE = American'
)
FROM DUAL;
(Если вы конвертируете специфические для языка термины, такие как имена месяцев, то хорошей практикой является включение nlsparam параметра nlsparam в функцию TO_DATE() и указание языка, который следует ожидать.)
Создание дат с помощью компонента времени
Преобразуйте его из строкового литерала с помощью TO_DATE() :
SELECT TO_DATE( '2000-01-01 12:00:00', 'YYYY-MM-DD HH24:MI:SS' ) FROM DUAL;
Или используйте литерал TIMESTAMP :
CREATE TABLE date_table(
date_value DATE
);
INSERT INTO date_table ( date_value ) VALUES ( TIMESTAMP '2000-01-01 12:00:00' );
Oracle будет неявным образом использовать TIMESTAMP для DATE при хранении в столбце DATE таблицы; однако вы можете явно CAST() значение CAST() для DATE :
SELECT CAST( TIMESTAMP '2000-01-01 12:00:00' AS DATE ) FROM DUAL;
Формат даты
В Oracle тип данных DATE не имеет формата; когда Oracle отправляет DATE в клиентскую программу (SQL / Plus, SQL / Developer, Toad, Java, Python и т. д.), он отправит 7- или 8-байты, которые представляют дату.
DATE которая не хранится в таблице (т.е. сгенерирована SYSDATE и имеет тип 13 при использовании команды DUMP() ) имеет 8 байтов и имеет структуру (числа справа являются внутренним представлением 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 который хранится в таблице («тип 12» при использовании команды DUMP() ) имеет 7 байтов и имеет структуру (числа справа являются внутренним представлением 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
Если вы хотите, чтобы дата имела определенный формат, вам нужно будет преобразовать ее в то, что имеет формат (т. Е. Строку). Клиент SQL может косвенно выполнять это или вы можете явно преобразовать значение в строку с использованием TO_CHAR( date, format_model, nls_params ) .
Преобразование дат в строку
Используйте TO_CHAR( date [, format_model [, nls_params]] ) :
(Примечание: если модель формата не NLS_DATE_FORMAT параметр сеанса NLS_DATE_FORMAT будет использоваться в качестве модели формата по умолчанию , это может быть различным для каждого сеанса, поэтому не следует полагаться. Хорошая практика всегда указывать модель формата.)
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 );
Затем:
SELECT TO_CHAR( date_value, 'YYYY-MM-DD' ) AS formatted_date FROM table_name;
Выходы:
FORMATTED_DATE
--------------
2000-01-01
2016-07-21
2016-07-21
А также:
SELECT TO_CHAR(
date_value,
'FMMonth d yyyy, hh12:mi:ss AM',
'NLS_DATE_LANGUAGE = French'
) AS formatted_date
FROM table_name;
Выходы:
FORMATTED_DATE
-----------------------------
Janvier 01 2000, 12:00:00 AM
Juillet 21 2016, 08:00:00 AM
Juillet 21 2016, 19:08:31 PM
Установка модели формата даты по умолчанию
Когда Oracle неявно преобразует из DATE в строку или наоборот (или когда TO_CHAR() или TO_DATE() явно вызываются без модели формата), параметр сеанса NLS_DATE_FORMAT будет использоваться в качестве модели формата при преобразовании. Если литерал не соответствует модели формата, будет создано исключение.
Вы можете просмотреть этот параметр, используя:
SELECT VALUE FROM NLS_SESSION_PARAMETERS WHERE PARAMETER = 'NLS_DATE_FORMAT';
Вы можете установить это значение в текущем сеансе, используя:
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
(Примечание: это не изменяет значение для других пользователей.)
Если вы полагаетесь на NLS_DATE_FORMAT для предоставления маски формата в TO_DATE() или TO_CHAR() вы не должны удивляться, когда ваши запросы ломаются, если это значение когда-либо изменяется.
Изменение того, как SQL / Plus или SQL Developer отображают даты
Когда SQL / Plus или SQL Developer отображают даты, они будут выполнять неявное преобразование в строку с использованием модели формата даты по умолчанию (см. Пример « Установка образца модели даты по умолчанию» ).
Вы можете изменить способ отображения даты, изменив параметр NLS_DATE_FORMAT .
Арифметика даты - разница между датами в днях, часах, минутах и / или секундах
В оракуле разница (в днях и / или их фракциях) между двумя DATE s может быть найдена с помощью вычитания:
SELECT DATE '2016-03-23' - DATE '2015-12-25' AS difference FROM DUAL;
Выводит количество дней между двумя датами:
DIFFERENCE
----------
89
А также:
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
Выводит долю дней между двумя датами:
DIFFERENCE
----------
1.0425
Разницу в часах, минутах или секундах можно найти, умножив это число на 24 , 24*60 или 24*60*60 соответственно.
Предыдущий пример можно изменить, чтобы получить дни, часы, минуты и секунды между двумя датами, используя:
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
);
(Примечание: TRUNC() используется вместо FLOOR() чтобы правильно обрабатывать отрицательные отличия.)
Выходы:
DAYS HOURS MINUTES SECONDS
---- ----- ------- -------
1 1 1 12
Предыдущий пример также может быть решен путем преобразования числовой разности в интервал, используя 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
);
Арифметика даты - разница между датами в месяцах или годах
Разницу в месяцах между двумя датами можно найти с помощью MONTHS_BETWEEN( date1, date2 ) :
SELECT MONTHS_BETWEEN( DATE '2016-03-10', DATE '2015-03-10' ) AS difference FROM DUAL;
Выходы:
DIFFERENCE
----------
12
Если разница включает частичные месяцы, тогда она вернет часть месяца на основании 31 дня в каждом месяце:
SELECT MONTHS_BETWEEN( DATE '2015-02-15', DATE '2015-01-01' ) AS difference FROM DUAL;
Выходы:
DIFFERENCE
----------
1.4516129
Из-за MONTHS_BETWEEN предполагающего 31 день в месяц, когда может быть меньше дней в месяц, это может привести к разным значениям различий, охватывающих границы между месяцами.
Пример:
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;
Выход:
JAN-FEB FEB-MAR MAR-APR APR-MAY
------- ------- ------- -------
0.03226 0.09677 0.03226 0.06452
Разницу в годах можно найти, разделив разницу в месяц на 12.
Извлечение года, месяца, дня, часа, минут или вторых компонентов даты
Компоненты года, месяца или дня типа данных DATE можно найти, используя 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;
Выходы:
YEAR MONTH DAY
---- ----- ---
2016 7 25
Компоненты времени (час, минута или секунда) могут быть найдены либо:
- Использование
CAST( datevalue AS TIMESTAMP )для преобразованияDATEвTIMESTAMPа затем с помощьюEXTRACT( [ HOUR | MINUTE | SECOND ] FROM timestampvalue ); или же - Использование
TO_CHAR( datevalue, format_model )для получения значения в виде строки.
Например:
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
);
Выходы:
HOURS MINUTES SECONDS
----- ------- -------
9 42 1
Временные зоны и летнее время
Тип данных DATE не обрабатывает часовые пояса или изменения в летнее время.
Или:
- используйте тип данных
TIMESTAMP WITH TIME ZONE; или же - обрабатывать изменения в логике вашего приложения.
DATE может быть сохранен как скоординированное универсальное время (UTC) и преобразован в текущий часовой пояс сеанса следующим образом:
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;
Если вы запустите ALTER SESSION SET TIME_ZONE = '+01:00'; то выход:
TIME
------------------------------------
2016-01-01 13:00:00.000000000 +01:00
и ALTER SESSION SET TIME_ZONE = 'PST'; то выход:
TIME
------------------------------------
2016-01-01 04:00:00.000000000 PST
Секундомер
Oracle не обрабатывает прыжки секунд . Дополнительную информацию см. В примечаниях к поддержке 2019397.2 и 730795.1 .
Получение дня недели
Вы можете использовать TO_CHAR( date_value, 'D' ) чтобы получить день недели.
Однако это зависит от параметра сеанса NLS_TERRITORY :
ALTER SESSION SET NLS_TERRITORY = 'AMERICA'; -- First day of week is Sunday
SELECT TO_CHAR( DATE '1970-01-01', 'D' ) FROM DUAL;
Выходы 5
ALTER SESSION SET NLS_TERRITORY = 'UNITED KINGDOM'; -- First day of week is Monday
SELECT TO_CHAR( DATE '1970-01-01', 'D' ) FROM DUAL;
Выходы 4
Чтобы сделать это независимо от настроек NLS , вы можете усечь дату до полуночи текущего дня (чтобы удалить любые доли дней) и вычесть дату, усеченную до начала текущей недели iso-week (которая всегда начинается в понедельник):
SELECT TRUNC( date_value ) - TRUNC( date_value, 'IW' ) + 1 FROM DUAL