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

*

*