Отслеживание даты и времени

Oracle поддерживает три типа данных (DATE, TIMESTAMP, INTERVAL) и ряд встроенных функций, которые мы рассмотрим ниже.

Выбираем нужную информацию из типа данных DATE

До Oracle 9i, только в одном типе данных (DATE) разрешалось хранить point-in-time значения (общий термин, который включает в себя информацию о дате и времени), даже сейчас Oracle не имеет независимых типов данных только для даты или только для времени, как в некоторых других языках. Хотя в Oracle уже добавили другие типы данных связанных со временем, DATE до сих пор считается наиболее удобным и простым в этой группе.

Тип данных DATE может содержать следующую информацию: век, год, месяц, день, час, минута, и секунда. Правильными считаются даты в диапазоне Январь 1, 4712 BC to Декабрь 31, AD 9999. Используйте следующий код для объявления переменных типа данных DATE:

declare
   variable1_dt DATE;...

Обявление переменной типа DATE очень простая задача. Вам не надо использовать параметры.

Однако для того чтобы взять часть информации из DATE-переменной вам необходимо будет воспользоваться функцией TO_CHAR с форматом даты показанном в листинге:

1
2
3
4
5
6
7
8
9
10
11
declare
   v_dt DATE :=sysdate;
   v_tx VARCHAR2(2000);
begin
   v_tx:=to_char(v_dt,'mm/dd/yyyy');
   DBMS_OUTPUT.put_line(v_tx);
   v_tx:=to_char(v_dt,'hh24:mi');
   DBMS_OUTPUT.put_line(v_tx);
   v_tx:=to_char(v_dt);
   DBMS_OUTPUT.put_line(v_tx);
end;

/02/12/2006
13:06
12-FEB-06

Примечания:
2 — Встроенная функция SYSDATE возвращает текущую дату и время сервера на котором распологается БД.
5 — В этой строке присутствует пример того как взять только дату из переменной.
7 — Эта строка отображает время в военном 24-часовом формате.
9 — Когда формат вывода даты не определен, Oracle использует собственный формат даты по умолчанию из инициализационного параметра NLS_DATE_FORMAT(‘DD-MON-RRRR’).
NLS расшифровывается как National Language Support (поддержка национальных языков), также известна как GlobalizationSupport. NLS позволяет вам конфигурировать базу данных таким образом чтобы соответствоватьтребованиям разных стран по использованию форматов дат и времени. Эти настройки находятся в представлениях NLS_SESSION_PARAMETERS и NLS_DATABASE_PARAMETERS.

Применение масок форматов

Для использования даты в строковом формате, необходимо воспользоваться функцией TO_CHAR,например, так:

v_string_tx := to_char(DATE variable[,format mask])

Функция TO_CHAR конвертирует дату в строку используя специальный формат маски. Таблица. Наиболее часто используемые опции форматирования.

Формат Значение
Y, YY, YYYY, YYYY Год (от одного до 4 разрядов)
RR, RRRR Округленный год (принимаются 2-х или 4-х разрядные значения и конвертируются в 2-х разрядные значения в соответсвующем веке)
MM, MONTH, MON
Месяц, имя месяца, сокращенное наименование
W, WW Неделя в месяце, неделя в году
D, DD, DDDD День недели, день месяца, день года
DAY, DY Название для недели (фиксированная длина: 9 символов), аббревиатура названия дня недели
HH, HH24 Час дня (1–12) или (0–23)
A.M./ AM Меридианальный идикатор с или без периодов
MI Минуты (0–59)
SS, SSSSS Секунды (0–59), секунды от полуночи (0–86399)

Вы можете использовать некоторые символы в качестве разделителей (например, запятая, точка, пробел, и точка с запятой) с маской формата, но все остальное необходимо заключать в двойные кавычке, например так:

1
2
3
4
5
6
declare
v_dt DATE :=sysdate;
v_tx VARCHAR2(2000);
   beginv_tx:=to_char(v_dt,'”Today is”: DAY');
   DBMS_OUTPUT.put_line(v_tx);
end;

Today is: SUNDAY

Примечание:
5 — Вы можете использовать двоеточие (:) без кавычек, но строка «Сегодня» должна быть в кавычках. Вы можете обернуть процесс, используя функцию TO_DATE для конвертирования строковых значений в значения даты используя надлежащие форматы, например:

v_date_dt := to_date(string variable[,format mask])

Формат DATE позволяет хранить любые временные данные от веков до секунд. Вам необходимо лишь указать желаемые единицы измерения.
Например:

1
2
3
4
5
6
7
8
9
10
11
declare
   v_dt DATE;
   v_tx VARCHAR2(2000);
begin
   v_dt := to_date(‘19:40','HH24:MI');
   v_tx := to_char(v_dt,'YYYY-MM-DD HH24:MI:SS');
   DBMS_OUTPUT.put_line(v_tx);
   v_dt := to_date(‘11-FEB-2006','DD-MON-YYYY');
   v_tx := to_char(v_dt,'YYYY-MM-DD HH24:MI:SS');
   DBMS_OUTPUT.put_line(v_tx);
   end;

2006-02-01 19:40:00
2006-02-11 00:00:00

Значения по умолчанию:

Oracle использует следующие правила для установки значения по умолчанию:
5 — Пропущенная дата по умолчанию является первым днем текущего месяца.
8 — Пропущенное время приравнивается к полночи текущего дня. Остальные правила:

  • Пропущенный год или месяц по умолчанию являются текущими.
  • Пропущенный день по умолчанию является первым днем месяца (если иной текущий месяц не указан).
  • Пропущенные часы, минуты, секунды по-умолчанию приравниваются к нулевому значению.

Проверка формата данных

1
2
3
4
5
6
7
8
CREATE OR REPLACE procedure p_format
IS
   v_dt DATE :=sysdate;
   v_tx VARCHAR2(2000);
begin
   v_tx:=to_char(v_dt,'Today is: DAY');
   DBMS_OUTPUT.put_line(v_tx);
end;

ERROR at line 1: ORA-01821: date format not recognized ➞15ORA-06512: at “SCOTT.P_FORMAT”, line 6 ORA-06512: at line 1SQL>

Примечания:
6 — Нет двойных кавычек вокруг строки “Today is” в маске формата, но процедура успешно создалась.
15 — Если вы попробуете запустить процедуру P_FORMAT, Oracle выдаст вам ошибку.

Проверяйте правильность при задании формата данных. Значительно проще проверять код на правильность на этапе разработки, нежели искать ошибки во время его работы.

Использование TIMESTAMP

Тип данных DATE имеет ряд ограничений. Поэтому Oracle ввел тип данных TIMESTAMP, который мы и рассмотрим.
Например, с помощью типа данных TIMESTAMP вы можете определить доли секунды (в DATE точность ограничивается секундами).

Пример объявления:

declarevariable1_ts TIMESTAMP[(precision)];...

Повышенная точность определения времени может быть полезна для работы с событиями происходящими в одно и то же время с точностью до секунды.

По умолчанию, Oracle хранит 6 точных цифр, но вы можете указать точность в диапазоне от 0 до 9. TIMESTAMP (0) равен DATE. В примере показано, как объявить TIMESTAMP тип данных:

declare
   v_ts TIMESTAMP(6):=systimestamp;
   v_tx VARCHAR2(2000);
begin
   v_tx:=to_char(v_ts,'HH24:MI:SS.FF6');
   DBMS_OUTPUT.put_line(v_tx);
end;

15:39:51.812000

Примечания:

2 — Для инициализации переменной необходимо использовать SYSTIMESTAMP, а не SYSDATE.
5 — новый элемент маски формата данных FF[1-9] предназначен для задания долей секунд. Если вы укажете точность меньше той что хранится в БД, Oracle использует технику округления результата выдачи идентичную округлению для чисел с плавающей точкой.

Использование TIMESTAMP с параметром TIMEZONE

Еще одно ограничение типа данных DATE заключается в том, что даже если у вас есть значение типа DATE, хранить часовой пояс, в котором
оно была создана невозможно.

Но вы можете хранить часовой пояс для TIMESTAMP данных используя параметр TIMEZONE, как показано в примере:

declarevariable1_ts TIMESTAMP[(precision)] WITH TIME ZONE;...

Oracle может определить time zone используя информацию с сервера БД либо с клиентского компьютера.
Вы можете сами посмотреть эти значения, используя встроенные функции DBTIMEZONE and SESSIONTIMEZONE, как показано в примере:

1
2
3
4
5
6
7
8
9
declare
   v_ts TIMESTAMP(6) WITH TIME ZONE :=CURRENT_TIMESTAMP;
   v_tx VARCHAR2(2000);
begin
   v_tx:=to_char(v_ts,'HH24:MI:SS.FF6 TZR');
   DBMS_OUTPUT.put_line(v_tx);
   v_tx:=to_char(v_ts,'TZH TZM');
   DBMS_OUTPUT.put_line(v_tx);
end;

17:50:42.828000 -05:00-05 00

Примечания:

2-3 — Встроенная функция CURRENT_TIMESTAMP поддерживает тип данных TIMESTAMP в сессионной (клиентской) time zone, но не в time zone базы данных.
6 — Формат TZR возвращает информацию о time zone региона.
В зависисмости от настроек БД, он может из себя представлять или разницу в часах и минутах между сессионной time zone и UTC (всемирное время, ранее время по Гринвичу) или название региона.
8 — Если вы хотите вернуть только разницу во времени в часах и минутах можно воспользоваться параметрами TZH и TZM.
12 — На этой строке мы видим результат работы скипта. Для компьютера, находящегося в Западном Стандартном часовом поясе, разница
во времени составляет 5 часов.

Если ваша система работает в нескольких часовых поясах, информация полученная из CURRENT_TIMESTAMP позволяет точнее определить время активности клиентов вашей БД.
В противном случае (не используя CURRENT_TIMESTAMP), вы не сможете дифференцировать время между 3 ч. дня Восточнго полушария и 3 ч. дня для временного пояса Тихого океана.

Хранение прошедшего времени с помощью типа данных INTERVAL

INTERVAL — еще один полезный тип данных введеный в Oracle 9i для того чтобы расширить функциональность типа данных DATE. Очень часто, вам не нужно хранить точку во времени, но время, прошедшее между точками, то есть интервал. Например, если вы хотите поддерживать услуги телефонной связи, вам необходимп сохранить день, когда был принят вызов и продолжительности звонка, но вам не нужно сохранять информацию о точном времени начала и окончания каждого вызова.

Единственная информация, которую вам нужна в данном случае — сколько времени человек потратил на вызов. Конечно, вы можете использовать две переменные (начало и конец дата/время) и некоторые другие способы, например, хранение часов, минут и секунд в независимых числовых столбцах. Но в как альтернатива, вы можете хранить интервал независимо, который можно будет самостоятельно обрабатывать позднее, как показано здесь:

1
2
3
4
5
6
7
8
declare
   v_start_ts TIMESTAMP:=to_timestamp(‘14:00:00','HH24:MI:SS');
   v_end_ts TIMESTAMP:=to_timestamp(‘15:12:24','HH24:MI:SS');
   v_delta_int INTERVAL DAY TO SECOND;
begin
   v_delta_int:=v_end_ts-v_start_ts;
   DBMS_OUTPUT.put_line(v_delta_int);
end;

+00 01:12:24.000000

INTERVAL содержит два типа данных которые позволят вам установить соответствующие более точные значения интервалов:

declare
   variable1_int INTERVAL YEAR[(precision)] TO MONTH;
   variable2_int INTERVAL DAY[(precision)] toSECOND[(precision)];...

Тип данных INTERVAL YEAR TO MONTH позволяет хранить и манипулировать интервалами типа годов и месяцев. Вы можете указать количество символов для хранения годов которые вы хотите хранить (по умолчанию 2, доступный диапазон 0–4). Тип данных INTERVAL DAY TO SECOND позволяет хранить и манипулировать интервалами типа дней, часов, минут и секунд. В этом случае, точность для дней позволяет вам ввести число символов для хранения, и точность для секунд определяет число символов для хранения долей секунд.

Работа с датами встроенных функций

Oracle предоставляет ряд встроенных функций для работы с типом данных DATE. Мы обсудим наиболее часто используемые из них ниже.

Функция EXTRACT

Функция EXTRACT позволяет выделить определенную часть даты/интервала/времени (только год, только месяц, и так далее до секунд) как показано на примере:

v_nr:= EXTRACT (TYPE from DATE|TIMESTAMP|INTERVAL value);

Функция EXTRACT всегда возвращает численное значение, таким образом если вам необходимо вернуть текстовое наименование месяца то необходимо воспользоваться

функция TO_CHAR. Но если вам необходимо выделить месяц из значения даты, функция EXTRACT быстрее и эффективнее TO_CHAR. Простой пример:

1
2
3
4
5
6
declare
   v_nr number;
begin
   v_nr:=EXTRACT(MONTH FROM sysdate);
   DBMS_OUTPUT.put_line(v_nr);
end;

В функцию EXTRACT можно передавать следующие параметры YEAR, MONTH, DAY, HOUR, MINUTE, и SECOND. Так же вы можете использовать типы данных TIME ZONE и TIMESTAMP.

Использование функции EXTRACT является примером высоко профессионального PL/SQL-кода. Хотя теже результаты вы можете получить используя функцию
TO_CHAR (например, TO_CHAR(SYSDATE,’MM’)), но обнаружить потом ошибку будет сложнее.

Функции TRUNC and ROUND

Встроенные функции TRUNC и ROUND, обычно применяемые к числовым значениям, так же работают с датами и временем так же как с числами. Функция TRUNC усекает дату до определенного уровня точности, в тоже время ROUND округляет дату до определенной единицы:

v_dt:= TRUNC (DATE|TIMESTAMP|INTERVAL value[,PRECISION]);
v_dt:= ROUND (DATE|TIMESTAMP|INTERVAL value[,PRECISION]);

В этих функциях вместо задания числовой точности предпочтительнее использовать маску формата даты. Например, маска ‘YYYY’ усечет дату до годов, а ‘MM’ до месяцев.

По умолчанию дата усекается до дней (‘DD’). Вы не можете использовать комбинацию форматов, то есть за раз вы можете использовать лишь одну.

Пример использования функций TRUNC и ROUND:

1
2
3
4
5
6
7
8
9
10
11
declare
   v_dt DATE;
   v_form_tx VARCHAR2(25):='YYYY-MM-DD HH24:MI:SS';
begin
   v_dt:=trunc(sysdate);
   DBMS_OUTPUT.put_line(to_char(v_dt,v_form_tx));
   v_dt:=trunc(sysdate,'YYYY');
   DBMS_OUTPUT.put_line(to_char(v_dt,v_form_tx));
   v_dt:=round(sysdate,'HH');
   DBMS_OUTPUT.put_line(to_char(v_dt,v_form_tx));
end;

2006-02-12 00:00:00
2006-01-01 00:00:00
2006-02-12 19:00:00

Примечание:

5 — функция TRUNC без маски формата даты округляет текущую дату до полночи.
7 — такой же вызов функции, но с маской ‘YYYY’ округляет дату до полуночи нового года.
9 — функция ROUND с маской ‘HH’ окурглит дату до начала часа.

TRUNC (value,’W') простейший путь получения первого дня недели.

Есть одна важная деталь при сипользовании дат: если точное время не имеет значения и вы хотите использовать запросы с сортировкой по конкртеной дате, всегда усекайте их.

Разумной альтернативой является построение function-based index (see the Oracle manuals) на усекаемое значение TRUNC(value). Это даст вам хороший механизм повышения производительности для поисковых запросов где время не так уж и важно, но вы все еще хотите сэкономить его для других запросов.

Функция ADD_MONTHS

Так как длина месяцев может различаться работать с точностями более чем день не так уж и просто. 29 дней могут быть более некоторого месяца, меньше, или равно. Oracle делает вашу жизнь проще предоставляя встроенные функции для работы с месяцами: ADD_MONTHS, MONTHS_BETWEEN, и LAST_DAY. Функция ADD_MONTHS добавляет целое месяцев к определенной дате.

v_dt:= ADD_MONTHS(date,integer);

1
2
3
4
5
6
7
8
9
declare
   v_dt DATE;
   v_form_tx VARCHAR2(25):=‘DD-MON-YYYY HH24:MI:SS';
begin
   v_dt:=add_months(sysdate,1);
   DBMS_OUTPUT.put_line(to_char(v_dt,v_form_tx));
   v_dt:=add_months(to_date(‘030130','RRMMDD'),1);
   DBMS_OUTPUT.put_line(to_char(v_dt,v_form_tx));
end;

12-MAR-2006 19:13:51
28-FEB-2003 00:00:00

Примечания:

6 — Этот код добавляет один месяц к текущему дню.
8 — Этот код добавляет один месяц к Январь 30, 2003.
12 — Результатом этой операции является, дата с текущим временем, но на один месяц в прошлое.
13 —

Вторая операция значительно более интересна так как вы добавляете месяц к дате которая не существует (Ведь не существует 30-го февраля). В этом случае Oracle возвращает полночь следующего дня результирующего месяца. Это поведение самая важная причина использования функции ADD_MONTHS. В противном случае, если вы просто добавите 30 дней, вы можете получить дату в Марте.

Функция LAST_DAY

Функция LAST_DAY возвращает последний день месяца от определенной даты.

v_dt:= LAST_DAY (date);

1
2
3
4
5
6
7
declare
   v_dt DATE;
   v_form_tx VARCHAR2(25):=‘DD-MON-YYYY HH24:MI:SS';
begin
   v_dt:=last_day(sysdate);
   DBMS_OUTPUT.put_line(to_char(v_dt,v_form_tx));
end;

28-FEB-2006 19:21:55

10 — Помните что каждый раз получая последний день месяца время сохраняется.
Не забудьте усечь дату если вам необходимо получить полночь последнего дня месяца.

Функция MONTHS_BETWEEN

v_nr:= MONTHS_BETWEEN(date1,date2);

Эта функция возвращает число месяцев между двумя датами. Если разница не является точной (целочисленной) вы получите число с
плавающей запятой где десятичная часть представляет остаток от деления N/31 где N число оставшихся дней. Если SYSDATE в районе начала Февраля, вы получите примерно такой результат:

1
2
3
4
5
6
declare
   v_nr number;
begin
   v_nr:=months_between(sysdate,trunc(sysdate,'Y'));
   DBMS_OUTPUT.put_line(v_nr);
end;

1.3810748954599761051373954599761051374

Так как число месяцев представлено числом с плавающей точкой вы всегда можете представить рисунок из дней, часов, минкт, и даже секунд, но технически это делать не рекомендуется. Используйте вместо этого тип данных интервал INTERVAL. Как правило резульатом этой функции является либо целое либо при необходимости усеченное число.

Оставить комментарий