Введение
В данной статье хотелось бы рассказать про недокументированный тип данных TIME, реализованный в Oracle. Новички в Oracle часто забывают, что тип данных DATE также содержит время, иногда пытаются создавать свои типы данных для хранения именно времени или вовсе хранят время в строковых типах данных (CHAR, VARCHAR2). В мои цели не входит обсуждение того, почему корпорация Oracle до сих пор официально не разрешает этот тип данных и не включает его в последующие релизы Oracle Server, я только покажу, как это работает. По моим данным, тип данных TIME существует в Oracle, начиная с версии 8.1.6, т.е. уже достаточно давно, все это время оставаясь сокрытым от широкой общественности, хотя упоминание некоторых с ним связанных функций легко обнаружить в пакете DBMS_STANDARD. Все проведенные эксперименты проверялись с незначительными оговорками на версиях 8.1.7.4, 9.2.0.7, 10.2.0.1. Итак, приступим.
Как включить тип данных TIME
Включение использования типа данных TIME можно добиться установкой события 10407 на уровне сессии. Без включения данного события тип данных TIME остается заблокированным, и все обращения к нему или к зависимым функциям будут заканчиваться различными ошибками. Ниже приведен листинг включения данного события и создания таблицы с единственным полем типа данных TIME(0).
Листинг 1
SCOTT@O102>exec dbms_output.put_line (sqlerrm(-10407));
ORA-10407: enable datetime TIME datatype creation
PL/SQL procedure successfully completed.
SCOTT@O102> --Create table with time datatype
SCOTT@O102>create table t (tm time);
create table t (tm time)
*
ERROR at line 1:
ORA-00902: invalid datatype
SCOTT@O102> --Set event 10407 and try again
SCOTT@O102>alter session set events '10407 trace name context forever, level 1';
Session altered.
SCOTT@O102>create table t (tm time);
Table created.
SCOTT@O102>desc t
Name Null? Type
----------------------------------------- -------- -------------------------
TM TIME(0)
SCOTT@O102>insert into t select to_time('01.02.'||level*5) from dual connect by
level<=5;
5 rows created.
SCOTT@O102>select * from t;
TM
---------------------------------------------------------------------------
01.02.05 AM
01.02.10 AM
01.02.15 AM
01.02.20 AM
01.02.25 AM
В паре к типу данных TIME, существует тип данных TIME WITH TIME ZONE, который дополнительно хранит еще часы и минуты временной зоны. Если обратить внимание, то на самом деле мы в предыдущем примере создали таблицу типа данных TIME (0) – в текущей реализации тип данных TIME может хранить и доли, меньшие секунды. То есть TIME (n) служит для хранения: часов, минут, секунд и 10n долей секунды (где n – целое от 0 до 9).
Связанные NLS-параметры
NLS-параметры, связанные с типом данных TIME, подчиняются абсолютно тем же правилам, что и прочие NLS-параметры (типов DATE, TIMESTAMP и т.д.). Как мне известно, есть всего два таких NLS-параметра: первый для TIME, второй для TIME WITH TIME ZONE. Рассмотрим небольшой пример.
Листинг 2
SCOTT@O102>col parameter for a30
SCOTT@O102>col value for a30
SCOTT@O102>select * from nls_session_parameters where parameter like '%TIME\_%' escape '\';
PARAMETER VALUE
------------------------------ ------------------------------
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
SCOTT@O102>desc t1
Name Null? Type
----------------------------------------- -------- -------------------------
TM TIME(6)
SCOTT@O102>select * from t1;
TM
---------------------------------------------------------------------------
01.02.03.456789 AM
SCOTT@O102>alter session set nls_time_format='HH24:MI:SSXFF';
Session altered.
SCOTT@O102>select * from t1;
TM
---------------------------------------------------------------------------
01:02:03.456789
Функции типа данных TIME
Приведу пример использования нескольких функций, их декларацию можно посмотреть в спецификации пакета DBMS_STANDARD.
Листинг 3
SCOTT@O102>alter session set nls_time_format='HH24:MI:SSXFF';
Session altered.
SCOTT@O102>alter session set nls_time_tz_format='HH24:MI:SSXFF TZH:TZM';
Session altered.
SCOTT@O102>col tm for a40
SCOTT@O102> --CURRENT_TIME - текущее время с временной зоной
SCOTT@O102>select current_time tm from dual;
TM
----------------------------------------
02:22:34 +06:00
SCOTT@O102> --LOCALTIME - локальное время
SCOTT@O102>select localtime tm from dual;
TM
----------------------------------------
02:23:29
SCOTT@O102>
--TO_TIME - преобразование строки к типу TIME, опциональный второй аргумент - NLS-format
SCOTT@O102>select to_time('01.02.03','hh24.mi.ss') tm from dual;
TM
----------------------------------------
01:02:03.000000000
SCOTT@O102> --TO_TIME_TZ - аналогичен TO_TIME, но возвращает TIME WITH TIME ZON
SCOTT@O102>select to_time_tz('01.02.03','hh24.mi.ss') tm from dual;
TM
----------------------------------------
01:02:03.000000000 +06:00
SCOTT@O102> --EXTRACT - действие, как и для прочих DATETIME типов данных
SCOTT@O102>col hour for 9999
SCOTT@O102>col min for 999
SCOTT@O102>col sec for 90.999999999
SCOTT@O102>with t as (select to_time('01.02.03.123456789','hh24.mi.ssxff') tm
2 from dual)
3 select extract (hour from tm) hour,
4 extract (minute from tm) min,
5 extract (second from tm) sec
6 from t
7 /
HOUR MIN SEC
----- ---- -------------
1 2 3.123456789
SCOTT@O102> --TO_CHAR - аналогична TO_CHAR (datetime)
SCOTT@O102>col tm for a20
SCOTT@O102>select to_char(current_time,'hh-mi AM "TZ" TZH:TZM') tm from dual;
TM
--------------------
09-06 PM TZ +06:00
Использование с другими типами данных
В заключении, приведем таблицу операций, которые мне удалось обнаружить над типом данных TIME.
Листинг 4
| Аргумент 1 |
Оператор |
Аргумент 2 |
Результат |
| TIME |
- |
TIME |
DSINTERVAL |
| TIME |
+ |
DSINTERVAL |
TIME |
| TIME |
- |
DSINTERVAL |
TIME |
