суббота, 27 ноября 2010 г.

Get a difference between dates (получить разницу между датами)

Распространенный вопрос, при вычитании получается число, и что с ним делать дальше часто никто не знает, либо начинают сочинять невероятные решения.

После появления в Oracle интервальных типов:
  • INTERVAL YEAR TO MONTH
  • INTERVAL DAY TO SECOND
жить стало проще и разницу дат можно представить в виде интервала INTERVAL DAY TO SECOND (в днях/часах/минутах/секундах/милисекундах), используя следующие функции:

Формат NUMTODSINTERVAL(n, 'interval_unit'):
  • NUMTODSINTERVAL(5, 'DAY')
  • NUMTODSINTERVAL(21, 'HOUR')
  • NUMTODSINTERVAL(10, 'MINUTE')
  • NUMTODSINTERVAL(15, 'SECOND')

Формат TO_DSINTERVAL('D HH:MI:SS'):
  • TO_DSINTERVAL('5 21:10:15')

Примеры:

alter session set nls_date_format = 'DD.MM.YYYY HH24:MI:SS';

set linesize 256

COLUMN DSINTERVAL2 FORMAT A20
COLUMN DSINTERVAL3 FORMAT A20

with dates as (
  select to_date('20.11.2010 23:10:05', 'DD.MM.YYYY HH24:MI:SS') date_begin,
         to_date('26.11.2010 20:20:20', 'DD.MM.YYYY HH24:MI:SS') date_end
    from dual
)
select d.*,
       --Разница дат в до появления интервальных типов
       date_end - date_begin date_diffirence,
       --Приведение к интервальному типу при помощи NUMTODSINTERVAL
       NUMTODSINTERVAL(date_end - date_begin, 'DAY') DSINTERVAL2,
       --Приведение к интервальному типу при помощи day(5) to second(0)
       (date_end - date_begin) day(5) to second(0) DSINTERVAL3,
       -- Интервал в строковом виде заданного формата
       to_char(extract(day from (date_end - date_begin) day(5) to second(0)), '99') || 'd' ||
       to_char(extract(hour from (date_end - date_begin) day(5) to second(0)), '99') || 'h' ||
       to_char(extract(minute from (date_end - date_begin) day(5) to second(0)), '99') || 'm' ||
       to_char(extract(second from (date_end - date_begin) day(5) to second(0)), '99') || 's' as DSNTERVAL_TO_STR4,
       --Вычитаем из даты интервал
       date_end - TO_DSINTERVAL('5 21:10:15') DATE_BEGIN2,
       --Прибавляем к дате интервал
       date_begin + TO_DSINTERVAL('5 21:10:15') DATE_END2
  from dates d
 where 1 = 1
   and ( 
         numtodsinterval(date_end - date_begin, 'DAY') between numtodsinterval(-10, 'DAY') and numtodsinterval(10, 'DAY')
         or
         (date_end - date_begin) day(5) to second(0) between numtodsinterval(-10, 'DAY') and numtodsinterval(10, 'DAY')
         or
         (date_end - date_begin) day(5) to second(0) between to_dsinterval('-10 00:00:00') and to_dsinterval('10 00:00:00')
       )
;

DATE_BEGIN            DATE_END              DATE_DIFFIRENCE DSINTERVAL2                                        DSINTERVAL3                                        DSNTERVAL_TO_STR4 DATE_BEGIN2           DATE_END2            
--------------------- --------------------- --------------- -------------------------------------------------- -------------------------------------------------- ----------------- --------------------- ---------------------
11/20/2010 23:10:05   11/26/2010 20:20:20        5.88211806 +05 21:10:15.000000                                +05 21:10:15.000000                                  5d 21h 10m 15s  11/20/2010 23:10:05   11/26/2010 20:20:20  
1 row selected.

воскресенье, 21 ноября 2010 г.

Time zone, работа с временными зонами

Недавно понадобилось работать с часовыми поясами: Московское время перевести на Киевское.

Обычно делают так:

alter session set nls_date_format = 'DD.MM.YYYY HH24:MI:SS';

COLUMN kiev_time FORMAT A40

select --Московское время
       to_date('22.11.2010 20:20:20', 'DD.MM.YYYY HH24:MI:SS') - 1 / 24 kiev_time
  from dual;

Session altered.

KIEV_TIME            
---------------------
22.11.2010 19:20:20  
1 row selected.


А вот как с помощью типа TIMESTAMP, полный перечень временных зон в документации:


--Список временных зон
SELECT tzname, tzabbrev FROM V$TIMEZONE_NAMES;

COLUMN kiev_time FORMAT A40

-- С московского времени на киевское
select FROM_TZ(
         CAST(to_date('22.11.2010 20:20:20', 'DD.MM.YYYY HH24:MI:SS') AS TIMESTAMP),
         'Etc/GMT+3'
       ) AT TIME ZONE 'Etc/GMT+2' kiev_time
  from dual;

KIEV_TIME                          
-----------------------------------
22-NOV-10 07.20.20.000000 PM ETC/GM
T+2                                
                                                                                
1 row selected.

COLUMN kiev_time FORMAT A40

-- Полученный TIMESTAMP представить в виде даты(тип DATE)
select CAST(
         FROM_TZ(
           CAST(to_date('22.11.2010 20:20:20', 'DD.MM.YYYY HH24:MI:SS') AS TIMESTAMP),
           'Etc/GMT+3'
         ) AT TIME ZONE 'Etc/GMT+2'
         as DATE) kiev_time
  from dual;

KIEV_TIME                               
----------------------------------------
22.11.2010 19:20:20                     
1 row selected.

В предыдущих примерах использовался GMT, есть другой вариант Europe/Moscow -> Europe/Kiev:

select CAST(
         FROM_TZ(
           CAST(to_date('22.11.2010 20:20:20', 'DD.MM.YYYY HH24:MI:SS') AS TIMESTAMP),
           'Europe/Moscow'
         ) AT TIME ZONE 'Europe/Kiev'
         as DATE) kiev_time
  from dual;

KIEV_TIME            
---------------------
22.11.2010 19:20:20  
1 row selected.


-- Смещение (TZ_OFFSET) следующих временных зон:
select TZ_OFFSET('Europe/Kiev') from dual;

TZ_OFFSET('EUROPE/KIEV')
------------------------
+02:00                  
1 row selected.

select TZ_OFFSET('Europe/Moscow') from dual;

TZ_OFFSET('EUROPE/MOSCOW')
--------------------------
+03:00                    
1 row selected.

-- Текущая временная зона для сессии
SELECT SESSIONTIMEZONE FROM DUAL;

-- Сменить параметр TIME_ZONE для сеанса на Московское
alter session set TIME_ZONE = '+03:00';

select sysdate, current_date, current_timestamp, localtimestamp
 from dual;

суббота, 13 ноября 2010 г.

AGGREGATE_STRING с задаваемым разделителем

  Когда-то возникла необходимость выводить в одну строку текстовые значения принадлежащие одной группе, все варианты решения данной задачи можно посмотреть здесь String Aggregation Techniques, на то время, два года назад, самым простым и практичным и понятным мне показался этот вариант User-Defined Aggregate Function, который я взял на вооружение. Остальные решения через курсор, COLLECT, появившуюся в Oracle 10g не применимы на большим объемах записей или ограничения на пользовательскую сессию, как в случае с open_cursors. А функция SYS_CONNECT_BY_PATH очень сложная для применения в контексте этой задачи.

  Через неделю меня не устроило что разделитель жестко задан константой (, - запятая), а мне понадобилось заюзать перевод каретки и я переписал агрегатную функцию, чтобы в качестве разделителя можно было передать любую строку длиной до 30-ти символов, для разделителя этого хватит с головой.
 
  Оказывается в агрегатную функцию, в качестве входящего параметра можно, вместо строкового типа, указать объект:

--CREATE OR REPLACE FUNCTION string_agg (p_input VARCHAR2)
--RETURN VARCHAR2 PARALLEL_ENABLE AGGREGATE USING t_string_agg;

CREATE OR REPLACE FUNCTION AGGREGATE_STRING (input t_value_delimiter)
return varchar2 parallel_enable aggregate using t_aggregate_string;

Чтобы функция AGGREGATE_STRING скомпилировалась сначала надо создать объект T_VALUE_DELIMITER:

CREATE OR REPLACE TYPE T_VALUE_DELIMITER as object (
  value varchar2(4000),
  delimiter varchar2(30),
  order member function match(other t_value_delimiter) return number
);
/

CREATE OR REPLACE TYPE BODY T_VALUE_DELIMITER is
  order member function match(other t_value_delimiter)
    return number is
  begin
    if value < other.value then
      return -1;
    elsif value > other.value then
      return 1;
    else
      return 0;
    end if;
  end;
END;
/

GRANT EXECUTE ON T_VALUE_DELIMITER TO PUBLIC;

CREATE PUBLIC SYNONYM T_VALUE_DELIMITER FOR T_VALUE_DELIMITER;

  Свойство value задано 4000 (максимальная длина типа VARCHAR2, которая может возвращаться из PL/SQL процедур и функций). Будьте внимательны, если у вас база создана в юникоде, то агрегатная функция сможет вернуть не более двух тысяч символов.
CREATE OR REPLACE TYPE T_AGGREGATE_STRING as object (

    str_agg varchar2(4000),
    delimiter varchar2(30),

    static function ODCIAggregateInitialize(sctx  in out t_aggregate_string)
                    return number,

    member function ODCIAggregateIterate   (self  in out t_aggregate_string,
                                            value in t_value_delimiter)
                    return number,

    member function ODCIAggregateTerminate (self         in     t_aggregate_string,
                                            return_value    out varchar2,
                                            flags        in number      )
                    return number,

    member function ODCIAggregateMerge(self in out t_aggregate_string,
                                       ctx2 in t_aggregate_string    )
                    return number
);
/

CREATE OR REPLACE TYPE BODY         T_AGGREGATE_STRING is

    static function ODCIAggregateInitialize(sctx in out t_aggregate_string)
        return number is
    begin
        sctx := t_aggregate_string(null, null);
        return ODCIConst.Success;
    end;

    member function ODCIAggregateIterate(
      self in out t_aggregate_string, value in t_value_delimiter)
        return number is
    begin
        delimiter := value.delimiter;
        str_agg := str_agg || value.delimiter || value.value;
        return ODCIConst.Success;
    end;

    member function ODCIAggregateTerminate(self in t_aggregate_string,
        return_value out varchar2, flags in number) return number is
    begin
        return_value := LTRIM(str_agg, delimiter);
        return ODCIConst.Success;
    end;

    member function ODCIAggregateMerge(self in out t_aggregate_string,
        ctx2 in t_aggregate_string) return number is
    begin
        str_agg := str_agg || delimiter || ctx2.str_agg;
        return ODCIConst.Success;
    end;
end;
/

GRANT EXECUTE ON T_AGGREGATE_STRING TO PUBLIC;

-- Синоним не создаю так как пользователь к этому объекту явно
-- никогда не обратится
--CREATE PUBLIC SYNONYM T_AGGREGATE_STRING FOR T_AGGREGATE_STRING;


Наша агрегатная функция:

CREATE OR REPLACE FUNCTION AGGREGATE_STRING (input t_value_delimiter)
return varchar2 parallel_enable aggregate using t_aggregate_string;
/
GRANT EXECUTE ON AGGREGATE_STRING TO PUBLIC;

CREATE PUBLIC SYNONYM AGGREGATE_STRING FOR AGGREGATE_STRING;

Пример:

COLUMN value FORMAT A40

select gumanoid, aggregate_string(t_value_delimiter(feature, ', ')) gumanoid_group
from (
      select 'звери' gumanoid, 'собаки' feature from dual
      union all
      select 'птицы' gumanoid, 'синицы' feature from dual
      union all
      select 'звери' gumanoid, 'тюлени' feature from dual
      union all
      select 'птицы' gumanoid, 'воробьи' feature from dual
      union all
      select 'птицы' gumanoid, 'пеликаны' feature from dual
      union all
      select 'звери' gumanoid, 'медведи' feature from dual
     )
group by gumanoid;


GUMANOID VALUE                                   
-------- ----------------------------------------
звери    собаки, тюлени, медведи                 
птицы    синицы, пеликаны, воробьи               

2 rows selected.

Скрипт для удаления объектов

DROP PUBLIC SYNONYM T_VALUE_DELIMITER;
DROP PUBLIC SYNONYM AGGREGATE_STRING;
 
DROP FUNCTION AGGREGATE_STRING;
DROP TYPE BODY T_AGGREGATE_STRING;
DROP TYPE T_AGGREGATE_STRING;
DROP TYPE BODY T_VALUE_DELIMITER;
DROP TYPE T_VALUE_DELIMITER;


В Oracle 11g Release 2 появилась встроенная функция LISTAGG, которая делает агрегирование строк очень простым, плюс в ней, помимо разделителя, можно еще задавать сортировку в пределах группы:

COLUMN employees FORMAT A50

SELECT deptno, LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS employees
FROM   emp
GROUP BY deptno;

    DEPTNO EMPLOYEES
---------- --------------------------------------------------
        10 CLARK,KING,MILLER
        20 ADAMS,FORD,JONES,SCOTT,SMITH
        30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

3 rows selected.

А в Oracle 10g также подойдет вариант с XMLAGG:

WITH Q AS
(
      select 'звери' gumanoid, 'собаки' feature from dual
      union all
      select 'птицы' gumanoid, 'синицы' feature from dual
      union all
      select 'звери' gumanoid, 'тюлени' feature from dual
      union all
      select 'птицы' gumanoid, 'воробьи' feature from dual
      union all
      select 'птицы' gumanoid, 'пеликаны' feature from dual
      union all
      select 'звери' gumanoid, 'медведи' feature from dual
)
SELECT   gumanoid,
         RTRIM(
            XMLAGG (XMLELEMENT(e, feature||'; ') ORDER BY feature).EXTRACT('//text()'),
            '; '
         ) AS concatval
    FROM q
GROUP BY gumanoid;

GUMANOID CONCATVAL 
-------- ------------------------
звери    медведи; собаки; тюлени
птицы    воробьи; пеликаны; синицы
                                                                                
2 rows selected.
Но LISTAGG и XMLAGG не позволяет использовать distinct, поэтому ниже привожу пример использования XMLQUERY для удаления дублей из результата функций LISTAGG и XMLAGG:
SELECT LISTAGG (id, ',') WITHIN GROUP (ORDER BY id) listagg_no_distinct,
       TO_CHAR (
          RTRIM (
             XMLAGG (XMLELEMENT (e, id, ',').EXTRACT ('//text()') ORDER BY id).GetClobVal (),
             ','))
          xmlagg_no_distinct,
       XMLTYPE.getStringVal (
          XMLQUERY (
             (   'string-join(distinct-values(('''
              || REPLACE (LISTAGG (id, ',') WITHIN GROUP (ORDER BY id),
                          ',',
                          ''',''')
              || ''')), ",")')
             RETURNING CONTENT))
          listagg_distinct,
       XMLTYPE.getStringVal (
          XMLQUERY (
             (   'string-join(distinct-values(('''
              || REPLACE (
                    TO_CHAR (
                       RTRIM (
                          XMLAGG (XMLELEMENT (e, id, ',').EXTRACT (
                                     '//text()') ORDER BY id).GetClobVal (),
                          ',')),
                    ',',
                    ''',''')
              || ''')), ",")')
             RETURNING CONTENT))
          xmlagg_distinct
  FROM (SELECT ROWNUM rn, CASE WHEN LEVEL IN (3, 4, 7) THEN LEVEL END AS id
              FROM DUAL
        CONNECT BY LEVEL < 10
        UNION ALL
        SELECT 10, 7 FROM DUAL
       );