Робота з датами в 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

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*