timestamp相减的累计计算

[liangtu]

时间类型是timestamp，需要统计在线时长=本次退出时间-本次登录时间，由于存在多个登录、退出，因此需要sum。
如果把timestamp转成date然后做减法，肯定没问题，但是有精度损失。
直接进行timestamp的计算，找了很多资料，好像只能按照字符串的方式进行分段的减法，比如天和天相减，小时和小时相减...，然后做加法。

但是，这样写太麻烦了，想请教有没有简单点的，直接对timestamp进行减法并sum的方法。

[居然有人说长得比我帅]

两timestamp的日期相减得出的是完整的年月日时分秒小数秒,将这些再转换成秒再sum，不麻烦吧

[shenlele088]

引用 1 楼 gioh0022 的回复:

两timestamp的日期相减得出的是完整的年月日时分秒小数秒,将这些再转换成秒再sum，不麻烦吧

怎么转换成秒呢？

[luoyoumou]

-- oralce 时间段求和：

CREATE TABLE t(
  t_logid NUMBER(18,0),                     -- 登录ID(会话ID)
  t_login timestamp default systimestamp,   -- 登录时间
  t_logout timestamp default systimestamp,  -- 退出时间
  t_bet_inout INTERVAL DAY TO SECOND        -- 本次在线时长
);

CREATE OR REPLACE TRIGGER trg_t
BEFORE INSERT OR UPDATE ON t
FOR EACH ROW
BEGIN
  :NEW.t_bet_inout:=(:NEW.t_logout-:NEW.t_login) DAY TO SECOND;
END;
/

INSERT INTO t(t_logid) VALUES(1);
UPDATE t set t_logout=systimestamp;
COMMIT;
SELECT * FROM t WHERE t_logid=1;

INSERT INTO t(t_logid) VALUES(2);
COMMIT;
UPDATE t set t_logout=systimestamp;
COMMIT;
SELECT * FROM t WHERE t_logid=1;

create or replace type day_to_second_sum_type as object
(
   total interval day(9) to second(9),

   static function
        ODCIAggregateInitialize(sctx IN OUT day_to_second_sum_type )
        return number,

   member function
        ODCIAggregateIterate(self IN OUT day_to_second_sum_type ,
                             value IN varchar2 )
        return number,

   member function
        ODCIAggregateTerminate(self IN day_to_second_sum_type,
                               returnValue OUT  varchar2,
                               flags IN number)
        return number,

   member function
        ODCIAggregateMerge(self IN OUT day_to_second_sum_type,
                           ctx2 IN day_to_second_sum_type)
        return number
);
/

create or replace type body day_to_second_sum_type
  is

  static function ODCIAggregateInitialize(sctx IN OUT day_to_second_sum_type)
  return number
  is
  begin
      sctx := day_to_second_sum_type( numtodsinterval( 0, 'SECOND' ) );
      return ODCIConst.Success;
  end;

  member function ODCIAggregateIterate(self IN OUT day_to_second_sum_type,
                                       value IN varchar2 )
  return number
  is
      l_value interval day(9) to second(9);
  begin
          l_value := value;
          -- dbms_output.put_line( l_value );
      self.total := self.total + l_value;
      return ODCIConst.Success;
  end;

  member function ODCIAggregateTerminate(self IN day_to_second_sum_type,
                                         returnValue OUT varchar2,
                                         flags IN number)
  return number
  is
  begin
      returnValue := self.total;
      return ODCIConst.Success;
  end;

  member function ODCIAggregateMerge(self IN OUT day_to_second_sum_type,
                                   ctx2 IN day_to_second_sum_type)
  return number
  is
  begin
    self.total := self.total + ctx2.total;
    return ODCIConst.Success;
  end;
end;
/

CREATE or replace
  FUNCTION ds_sum(input varchar2)
  RETURN varchar2
  PARALLEL_ENABLE AGGREGATE USING day_to_second_sum_type;
/

-- 求“总在线时长”：
select to_dsinterval( ds_sum(t_bet_inout) ) sum
from t;

[luoyoumou]

更详细内容，请参考： http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5269744394385

[居然有人说长得比我帅]

引用 2 楼 shenlele088 的回复:

[quote=引用 1 楼 gioh0022 的回复:] 两timestamp的日期相减得出的是完整的年月日时分秒小数秒,将这些再转换成秒再sum，不麻烦吧

怎么转换成秒呢？ [/quote] substr各种截断取成秒啊~~

[liangtu]

引用 3 楼 gioh0022 的回复:

[quote=引用 2 楼 shenlele088 的回复:] [quote=引用 1 楼 gioh0022 的回复:] 两timestamp的日期相减得出的是完整的年月日时分秒小数秒,将这些再转换成秒再sum，不麻烦吧

怎么转换成秒呢？ [/quote] substr各种截断取成秒啊~~[/quote] 跟我之前实现的一样，字符串截取，没有更好的办法了？

[居然有人说长得比我帅]

引用 4 楼 liangtu33 的回复:

[quote=引用 3 楼 gioh0022 的回复:] [quote=引用 2 楼 shenlele088 的回复:] [quote=引用 1 楼 gioh0022 的回复:] 两timestamp的日期相减得出的是完整的年月日时分秒小数秒,将这些再转换成秒再sum，不麻烦吧

怎么转换成秒呢？ [/quote] substr各种截断取成秒啊~~[/quote] 跟我之前实现的一样，字符串截取，没有更好的办法了？[/quote] 你的是截了再减，这是减了再截，要是觉得麻烦你可以写个过程或函数做一下，应该没有其他

[luoyoumou]

-- 10.9.5 时间间隔的加减运算 ( P291 )
-- 和日期时间值不一样的是，把两个时间间隔进行加法运算是很有意义的。减法运行也同样有意义。要对两个时间间隔进行加减运行，你需要记住的一个规则就是：
-- 两个时间间隔的类型必须是相同类型的。比如：
DECLARE
  dts1 INTERVAL DAY TO SECOND := '2 3:4:5.6';
  dts2 INTERVAL DAY TO SECOND := '1 1:1:1.1';

  ytm1 INTERVAL YEAR TO MONTH := '2-10';
  ytm2 INTERVAL YEAR TO MONTH := '1-1';

  days1 NUMBER := 3;
  days2 NUMBER := 1;
BEGIN
  DBMS_OUTPUT.PUT_LINE(dts1 - dts2);
  DBMS_OUTPUT.PUT_LINE(ytm1 - ytm2);
  DBMS_OUTPUT.PUT_LINE(days1 - days2);
END;
/