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

При отображении информации о времени или датах в 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

Удачи...