(495) 925-0049, ITShop интернет-магазин 229-0436, Учебный Центр 925-0049
  Главная страница Карта сайта Контакты
Поиск
Вход
Регистрация
Рассылки сайта
 
 
 
 
 

Oracle: Работа с датами в Oracle

Источник: it-tales
it-tales

При отображении информации о времени или датах в Oracle критично, какую версию сервера вы используете. Например, начиная с версии 9, стало возможным представление моментов времени и временных интервалов используя типы данных ANSI SQL . Например, timestamp and interval . Ранние версии использовали тип данных date , с точностью одна секунда и временными интервалами как числа (где 1=один день).

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

Выборка по дате

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

CREATE TABLE USERS (
USER_ID INTEGER PRIMARY KEY,
NICKNAME VARCHAR(50),
REALNAME VARCHAR(50) NOT NULL,
EMAIL VARCHAR(100) NOT NULL UNIQUE,
PASSWORD VARCHAR(30) NOT NULL,
-- точность 1 секунда
REGISTRATION_DATE TIMESTAMP(0)
);

-- добавляем тестовые данные 
INSERT INTO USERS
(USER_ID, NICKNAME, REALNAME, EMAIL, PASSWORD, REGISTRATION_DATE)
VALUES
(1,'DBA','Елена','elena@all-oracle.ru','qwerty',
TO_TIMESTAMP('2009-06-13 09:15:00','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO USERS
(USER_ID, NICKNAME, REALNAME, EMAIL, PASSWORD, REGISTRATION_DATE)
VALUES
(2,'User','Виталий','vitaly@all-oracle.ru','qwerty',
TO_TIMESTAMP('2009-06-13 15:18:22','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO USERS
(USER_ID, NICKNAME, REALNAME, EMAIL, PASSWORD, REGISTRATION_DATE)
VALUES
(3,'Junior','Олег','oleg@all-oracle.ru','qwerty',
TO_TIMESTAMP('2009-06-16 10:11:52','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO USERS
(USER_ID, NICKNAME, REALNAME, EMAIL, PASSWORD, REGISTRATION_DATE)
VALUES
(4,'XXX','Александр','alexandr@all-oracle.ru','qwerty',
TO_TIMESTAMP('2009-06-16 13:01:36','YYYY-MM-DD HH24:MI:SS'));

Теперь выберем пользователей, которые зарегистрировались в течение последнего дня:
COLUMN EMAIL FORMAT A35
COLUMN REGISTRATION_DATE FORMAT A25
SELECT EMAIL, REGISTRATION_DATE
FROM USERS
WHERE REGISTRATION_DATE > CURRENT_DATE - INTERVAL '1' DAY;
 
EMAIL REGISTRATION_DATE
----------------------------------- -------------------------
oleg@all-oracle.ru 16-JUN-09 10.11.52 AM
alexandr@all-oracle.ru 16-JUN-09 01:01:36 PM

или, в зависимости от национальных настроек
EMAIL                               REGISTRATION_DATE
----------------------------------- -------------------------
oleg@all-oracle.ru 16.06.09 10:11:52
alexandr@all-oracle.ru 16.06.09 13:01:36

Для явного задания формата вывода даты, перепишем предложение так:
ALTER SESSION
SET NLS_TIMESTAMP_FORMAT =
'YYYY-MM-DD HH24:MI:SS';
 
SELECT EMAIL, REGISTRATION_DATE
FROM USERS
WHERE REGISTRATION_DATE > CURRENT_DATE - INTERVAL '1' DAY;
 
EMAIL REGISTRATION_DATE
----------------------------------- -------------------------
oleg@all-oracle.ru 2009-06-16 10:11:52
alexandr@all-oracle.ru 2009-06-16 13:01:36

Кроме того, по своему желанию, вы можете изменять интервал, увеличивая или уменьшая его, в зависимости от задачи. Например:
SELECT EMAIL, REGISTRATION_DATE 
FROM USERS
WHERE REGISTRATION_DATE > CURRENT_DATE - INTERVAL '1' HOUR;
 
EMAIL REGISTRATION_DATE
----------------------------------- -------------------------
anna_cat@all-oracle.ru 16.06.09 19:44:09

или
SELECT EMAIL, REGISTRATION_DATE 
FROM USERS
WHERE REGISTRATION_DATE > CURRENT_DATE - INTERVAL '1' MINUTE;
 
no ROWS selected

или
SELECT EMAIL, REGISTRATION_DATE 
FROM USERS
WHERE REGISTRATION_DATE > CURRENT_DATE - INTERVAL '1' SECOND;
 
no ROWS selected

Вы можете явно указывать формат для даты и времени:
SELECT EMAIL, TO_CHAR(REGISTRATION_DATE,'Day, Month DD, YYYY') AS REG_DAY
FROM USERS
ORDER BY REGISTRATION_DATE;
 
EMAIL REG_DAY
----------------------------------- ------------------------------
elena@all-oracle.ru Суббота , Июнь 13, 2009
vitaly@all-oracle.ru Суббота , Июнь 13, 2009
oleg@all-oracle.ru Вторник , Июнь 16, 2009
alexandr@all-oracle.ru Вторник , Июнь 16, 2009

В результате видим, мягко говоря, неудобоваримое форматирование. Исправляем ситуацию, переписав SQL предложение так:
SELECT EMAIL, 
TRIM(TO_CHAR(REGISTRATION_DATE,'Day')) // ', ' //
TRIM(TO_CHAR(REGISTRATION_DATE,'Month')) // ' ' //
TRIM(TO_CHAR(REGISTRATION_DATE,'DD, YYYY')) AS REG_DAY
FROM USERS
ORDER BY REGISTRATION_DATE;
 
EMAIL REG_DAY
----------------------------------- ------------------------------
elena@all-oracle.ru Суббота, Июнь 13, 2009
vitaly@all-oracle.ru Суббота, Июнь 13, 2009
oleg@all-oracle.ru Вторник, Июнь 16, 2009
alexandr@all-oracle.ru Вторник, Июнь 16, 2009

Подводные камни

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

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';

-- старый вариант 
SELECT ADD_MONTHS(TO_DATE('2009-06-20','YYYY-MM-DD'),-1) FROM DUAL;
 
ADD_MONTHS
----------
2009-05-20

-- новый вариант
SELECT TO_TIMESTAMP('2003-07-31','YYYY-MM-DD') - INTERVAL '1' MONTH FROM dual;
 
ERROR AT line 1:
ORA-01839: DATE NOT valid FOR MONTH specified

-- старый вариант 
SELECT TO_DATE('2009-06-20','YYYY-MM-DD') - 100 FROM DUAL;
 
TO_DATE('2
----------
2009-03-12

-- новый вариант
SELECT TO_TIMESTAMP('2009-06-20','YYYY-MM-DD') - INTERVAL '100' DAY FROM DUAL;
 
ERROR AT line 1:
ORA-01873: the leading precision OF the INTERVAL IS too small

-- новый вариант (точность "(3)")
SELECT TO_TIMESTAMP('2009-06-20','YYYY-MM-DD') - INTERVAL '100' DAY(3) FROM DUAL;
 
TO_TIMESTAMP('2009-06-20','YYYY-MM-DD')-INTERVAL'100'DAY(3)
---------------------------------------------------------------------------
2009-03-12 00:00:00

Маленькие неприятности

Вычисление временных интервалов может быть довольно неприятной задачей, поскольку в стандартном SQL нет возможности обратится к значению этой колонки из предыдущий строки в отчете. Это легко сделать в любом языке программирования, например C#, Visual Basic, Java или Delphi, которые могут прочитать базу данных, и затем сделать это своими средствами не прибегая к SQL.

Добавим еще несколько строк к нашей таблице со списком пользователей:

INSERT INTO USERS
(USER_ID, NICKNAME, REALNAME, EMAIL, PASSWORD, REGISTRATION_DATE)
VALUES
(5,'Kate','Екатерина','kate@all-oracle.ru','qwerty',
TO_TIMESTAMP('2009-06-16 06:00:00','YYYY-MM-DD HH24:MI:SS'));
 
INSERT INTO USERS
(USER_ID, NICKNAME, REALNAME, EMAIL, PASSWORD, REGISTRATION_DATE)
VALUES
(6,'Cat','Анна','anna_cat@all-oracle.ru','qwerty',
TO_TIMESTAMP('2009-06-16 19:44:09','YYYY-MM-DD HH24:MI:SS'));

Например, нам интересна средняя продолжительность времени между регистрацией пользователей:
SELECT REGISTRATION_DATE
FROM USERS
ORDER BY REGISTRATION_DATE;
 
REGISTRATION_DATE
-------------------------
2009-06-13 09:15:00
2009-06-13 15:18:22
2009-06-16 06:00:00
2009-06-16 10:11:52
2009-06-16 13:01:36
2009-06-16 19:44:09

Перепишем запрос с объединением:
COLUMN R1 FORMAT A21
COLUMN R2 FORMAT A21
 
SELECT U1.REGISTRATION_DATE AS R1,
U2.REGISTRATION_DATE AS R2
FROM USERS U1, USERS U2
WHERE U2.USER_ID = (SELECT MIN(USER_ID) FROM USERS
WHERE REGISTRATION_DATE > U1.REGISTRATION_DATE)
ORDER BY R1;
 
R1 R2
--------------------- ---------------------
2009-06-13 09:15:00 2009-06-13 15:18:22
2009-06-13 15:18:22 2009-06-16 10:11:52
2009-06-16 06:00:00 2009-06-16 10:11:52
2009-06-16 10:11:52 2009-06-16 13:01:36
2009-06-16 13:01:36 2009-06-16 19:44:09

Для обеспечения уникальности используем колонку USER_ID , поскольку время и дата регистрации не обеспечивают уникальность, т.к. возможна ситуация одновременной регистрации двух пользователей.

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

COLUMN REG_GAP FORMAT A21
 
SELECT U1.REGISTRATION_DATE AS R1,
U2.REGISTRATION_DATE AS R2,
U2.REGISTRATION_DATE-U1.REGISTRATION_DATE AS REG_GAP
FROM USERS U1, USERS U2
WHERE U2.USER_ID = (SELECT MIN(USER_ID) FROM USERS
WHERE REGISTRATION_DATE > U1.REGISTRATION_DATE)
ORDER BY R1;
 
R1 R2 REG_GAP
--------------------- --------------------- ---------------------
2009-06-13 09:15:00 2009-06-13 15:18:22 +000000000 06:03:22
2009-06-13 15:18:22 2009-06-16 10:11:52 +000000002 18:53:30
2009-06-16 06:00:00 2009-06-16 10:11:52 +000000000 04:11:52
2009-06-16 10:11:52 2009-06-16 13:01:36 +000000000 02:49:44
2009-06-16 13:01:36 2009-06-16 19:44:09 +000000000 06:42:33

Интервал для каждой колонки возвращает день, часы, минуты и секунды. По этим временным точкам можно рассчитать средний интервал. Для этого напишем такой запрос:
SELECT AVG(REG_GAP)
FROM
(SELECT
U1.REGISTRATION_DATE AS R1,
U2.REGISTRATION_DATE AS R2,
U2.REGISTRATION_DATE-U1.REGISTRATION_DATE AS REG_GAP
FROM USERS U1, USERS U2
WHERE U2.USER_ID = (SELECT MIN(USER_ID) FROM USERS
WHERE REGISTRATION_DATE > U1.REGISTRATION_DATE));
 
ERROR AT line 1:
ORA-00932: inconsistent datatypes: expected NUMBER got INTERVAL DAY TO SECONDS

Oracle выдал ошибку. В чем же дело? К сожалению Oracle не оказался достаточно умным, чтобы рассчитать среднее значение временного интервала. И как бы ни было печально, рассчитать среднее значение оказалось не так просто как хотелось бы.

Если у вас есть желание, то вы можете переписать предложение SQL в таком виде:

SELECT AVG(REG_GAP)
FROM
(SELECT
U1.REGISTRATION_DATE AS R1,
U2.REGISTRATION_DATE AS R2,
TO_DATE(TO_CHAR(U2.REGISTRATION_DATE,'YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS')
- TO_DATE(TO_CHAR(U1.REGISTRATION_DATE,'YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS')
AS REG_GAP
FROM USERS U1, USERS U2
WHERE U2.USER_ID = (SELECT MIN(USER_ID) FROM USERS
WHERE REGISTRATION_DATE > U1.REGISTRATION_DATE));
 
AVG(REG_GAP)
------------
,722363426

Если вы хотите использовать такие дикие запросы, то пожалуйста, но в данном случае целесообразнее создать представление:
CREATE VIEW REGISTRATION_INTERVALS
AS
SELECT
U1.REGISTRATION_DATE AS R1,
U2.REGISTRATION_DATE AS R2,
TO_DATE(TO_CHAR(U2.REGISTRATION_DATE,'YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS')
- TO_DATE(TO_CHAR(U1.REGISTRATION_DATE,'YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS')
AS REG_GAP
FROM USERS U1, USERS U2
WHERE U2.USER_ID = (SELECT MIN(USER_ID) FROM USERS
WHERE REGISTRATION_DATE > U1.REGISTRATION_DATE);

И теперь, можно посчитать среднее значение в минутах:
SELECT 24*60*AVG(REG_GAP) AS AVG_GAP_MINUTES 
FROM REGISTRATION_INTERVALS;
 
AVG_GAP_MINUTES
---------------
1040,20333

Удачи...

Ссылки по теме


 Распечатать »
 Правила публикации »
  Обсудить материал в конференции Oracle »
Написать редактору 
 Рекомендовать » Дата публикации: 22.06.2012 
 

Магазин программного обеспечения   WWW.ITSHOP.RU
Oracle Database Personal Edition Named User Plus License
Oracle Database Standard Edition 2 Named User Plus License
Oracle Database Standard Edition 2 Processor License
Oracle Database Personal Edition Named User Plus Software Update License & Support
EMS Data Export for PostgreSQL (Business) + 1 Year Maintenance
 
Другие предложения...
 
Курсы обучения   WWW.ITSHOP.RU
 
Другие предложения...
 
Магазин сертификационных экзаменов   WWW.ITSHOP.RU
 
Другие предложения...
 
3D Принтеры | 3D Печать   WWW.ITSHOP.RU
 
Другие предложения...
 
Новости по теме
 
Рассылки Subscribe.ru
Информационные технологии: CASE, RAD, ERP, OLAP
Новости ITShop.ru - ПО, книги, документация, курсы обучения
Программирование на Microsoft Access
CASE-технологии
Реестр Windows. Секреты работы на компьютере
Программирование в AutoCAD
СУБД Oracle "с нуля"
 
Статьи по теме
 
Новинки каталога Download
 
Исходники
 
Документация
 
Обсуждения в форумах
Access, Ключевое поле, Счетчик (10)
Подскажите пожалуйста, как изменить в Access в ключевом поле (счетчик последовательно),...
 
Решение задач на оптимизацию с помощью MS Excel (4)
Zdravstvuyte, ya pro4itala danniy material, no ne smogla ponat kakie formuli ispolzovalis v...
 
Отличается ли ДрифтКазино от беттинга? (4)
Друзья, давно заметил, что на Дрифте уже несколько месяцев во всю рекламируется и предлагается...
 
Сайт инструмент (1)
Я бывший программист пользовался 1 сайтом проверенным он мне действительно помог я блогодоря...
 
Где взять лицензионный ключ для AllFusion Process Modeler (BPwin) 7? (5)
Выручайте!!! где найти ключ, ужасно срочно нужна программа. заранее спасибо!
 
 
 



    
rambler's top100 Rambler's Top100