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

Удачи...

Страница сайта http://test.interface.ru
Оригинал находится по адресу http://test.interface.ru/home.asp?artId=29471