Источник: it-tales
it-tales
При отображении информации о времени или датах в Oracle критично, какую версию сервера вы используете. Например, начиная с версии 9, стало возможным представление моментов времени и временных интервалов используя типы данных ANSI SQL . Например, timestamp and interval . Ранние версии использовали тип данных date , с точностью одна секунда и временными интервалами как числа (где 1=один день).
Теперь выберем пользователей, которые зарегистрировались в течение последнего дня:
или, в зависимости от национальных настроек
Для явного задания формата вывода даты, перепишем предложение так:
Кроме того, по своему желанию, вы можете изменять интервал, увеличивая или уменьшая его, в зависимости от задачи. Например:
или
или
Вы можете явно указывать формат для даты и времени:
В результате видим, мягко говоря, неудобоваримое форматирование. Исправляем ситуацию, переписав SQL предложение так:
Подводные камни
Маленькие неприятности
Например, нам интересна средняя продолжительность времени между регистрацией пользователей:
Перепишем запрос с объединением:
Для обеспечения уникальности используем колонку USER_ID , поскольку время и дата регистрации не обеспечивают уникальность, т.к. возможна ситуация одновременной регистрации двух пользователей.
Интервал для каждой колонки возвращает день, часы, минуты и секунды. По этим временным точкам можно рассчитать средний интервал. Для этого напишем такой запрос:
Oracle выдал ошибку. В чем же дело? К сожалению Oracle не оказался достаточно умным, чтобы рассчитать среднее значение временного интервала. И как бы ни было печально, рассчитать среднее значение оказалось не так просто как хотелось бы.
Если вы хотите использовать такие дикие запросы, то пожалуйста, но в данном случае целесообразнее создать представление:
И теперь, можно посчитать среднее значение в минутах:
Удачи...
Если вы используете новые версии сервера 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
Удачи...