Master Yavor from Tryavna

IV…

И така, от първата част стана ясно, че искам да получа много точно колко време е отнел даден процес, като имам дата на започването и завършванвето му. Първия интересен момент, за който се сетих е какво правим при промяната на часовника през пролетта и есента (т.нар. DST). За щастие Oracle се справя автоматично с този проблем когато се използва „TIMESTAMP WITH LOCAL TIMEZONE“ (TSLTZ) (metalnik note 359119.1).

Понеже ми е много лесно да работя с тип DATE (там нещата са много прости), отначало си направих една функция, която да превръща подадените и TSLTZ в DATE и после – както си знам. Когато се извадят две DATE полета, резултата е дробно число, което се обаработва много лесно: цялата част е разликата между двете дати в дни, а дробната – в части от деня. Т.е. ако умножа (datе2 - datе1) * (24 * 60 * 60), получавам точно разликата в секунди.

Тук има две подробности: първо, така губим точността до секунда и второ, губим DST информацията. С второто се справих като нормализирах TSLTZ до гринуичко време преди да ги обърне в тип DATE:

create or replace function ts_diff_to_second(t1 in timestamp, t2 in timestamp) return number is
-- Gives the difference, in seconds, between 2 timestamps
-- Created by yavor Ivanov
 dat1 date;
 dat2 date;
 utc_t1 timestamp;
 utc_t2 timestamp;
 res number;
begin
  -- first we convert the timestamps to GMT to prevent DST problems
  utc_t1 := SYS_EXTRACT_UTC(t1);
  utc_t2 := SYS_EXTRACT_UTC(t2);
  -- then we convert the timestamps to dates, so we can easily substract them
  dat1 := utc_t1;
  dat2 := utc_t2;
  -- now we substract and convert to seconds
  res := (dat2-dat1)*24*60*60;
  return(res);
end;

До тук добре. Тази функция работи много хубаво и за повечето практически случаи е достатъчна. Обаче в нашата система доста неща се случват за периоди, по-малки от една секунда. За това задълбах в аритметиката на TSLTZ.

Когато се извадят две TSLTZ полета, резултата не е число, а е от изкелиферчения тип INTERVAL DAY TO SECOND. Характерното за този тип е че ако не си му свикнал, е трудно да боравиш с него 🙂

Първо да видим как изглежда резултата:

SQL> select send_time - receive_time Total_time
  2    from documents_proba
  3  where idDocument = 4379451;
 
TOTAL_TIME
-------------------
+000000000 00:00:05

Това идва да ни каже, че между пристигането и изпращането на този документ са минали +000000000 дни, 00 часа, 00 минути и 05 секунди. Първото, което прави впечатление тук е, че пак ни няма милисекундите 🙁

Втори опит:

SQL> select to_char(send_time - receive_time, 'FF') Total_time
  2    from documents_proba
  3  where idDocument = 4379451;
 
TOTAL_TIME
---------------------------
+000000000 00:00:05.828000

С това заклинание видяхме четири неща:
– частите от секундата си ги има, не са изгубени
– при първия пример oracle даже не си играе да закръгля, а направо отрязва частите от секундата (5.828 става на 5)
– to_char работи (долу-горе) и за INTERVAL, макар в документацията да не е описано как
– FF, което във форматирането на to_char би трябвало да връща само частите от секундата, наистина ги показва, но показва и всички друго

Тук някой може да си помисли, че съм тръгнал да parse-вам този резултат. Но истината е, че има и по-лесен начин. Има една функция EXTRACT, която със своя странен синтаксис ни позлвоява да получим части от interval (или друг тип) променлива.

Ето следващия пример:

SQL> select extract(second from send_time - receive_time) Total_time
  2    from documents_proba
  3  where idDocument = 4379451;
 
TOTAL_TIME
----------
     5,828

Както виждате, синтаксиса наистина е… абе нека го кажем „новаторски“. Вместо прости параметри функцията поема нещо като команда на английски – в случая извади_ми(секундите от този_израз). Ето още няколко красиви приемра от документацията:
EXTRACT(month FROM order_date)
EXTRACT(YEAR FROM TO_DATE(hire_date, 'DD-MON-RR'))
EXTRACT(TIMEZONE_REGION FROM TIMESTAMP '1999-01-01 10:00:00 -08:00')
EXTRACT(DAY FROM (SYSDATE - order_date) DAY TO SECOND )

И така… щастлив, че вече си имам секундите, настъпих следващата мотика. Ако интервала между двете TSLTZ полета е, да кажем, 02:34:12.323 (т.е. 2 часа, 34 минути и 12.323 секунди), extract(second from …) ще получим само секундите, т.е. 12.323. А това не е добре. За това функцията трябва да взема дните, часовете и минутите и да ги умножава по каквото трябва. В крайна сметка се получи следната функция:

create or replace function ts_diff_to_second2(t1 in timestamp, t2 in timestamp) return number is
  -- Gives the difference, in seconds and up to milisecond, between 2 timestamps
  -- Created by yavor Ivanov
  dif INTERVAL DAY TO SECOND;
  res number;
begin
  -- get the difference
  dif := t2 - t1;
  -- convert every element to seconds
  res := extract(day from dif) * 24 * 60 * 60;
  res := res + (extract(hour from dif) * 60 * 60);
  res := res + (extract(minute from dif) * 60);
  res := res + extract(second from dif);
  return(res);
end;

При миграция към Oracle 11g има една малка, но дразнеща мотичка, свързана с timezone файла, използван от БД. Не мога да разбера защо DBUA, който прави толкова много и сложни неща, не се справя с това. Почти съм сигурен, че проблемите са политически, но не мога да схвана какви са.

Както и да е. Ако мигрирате от нещо по-старо от 10.2.0.4 към 11g, трябва първо да си оправите timezone файла. За всички относително съвременни patchsets (примерно 10.2.0.3 или 10.1.0.5) това става лесно с прилагането на patch bundle с opatch. Но ако имате шанса да сте на нещо по-старичко, като 10.1.0.4, нещата загрубяват.

Ето тук е много полезна следната metalink note: Applying „version 4“ Time Zone Files on an Oracle Database. В нея има една красива табличка с линкове и обяснения за коя версия какво се прави.

Enjoy 🙂