И така, от първата част стана ясно, че искам да получа много точно колко време е отнел даден процес, като имам дата на започването и завършванвето му. Първия интересен момент, за който се сетих е какво правим при промяната на часовника през пролетта и есента (т.нар. 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;