Блог

У меня есть следующий запрос для вычисления метки времени суммирования

 SELECT SUM(TIME_SPENT) FROM
(
 select a - b as time_spent from tbl1 where name = 'xxx'
 union all
select c - d as time_spent from tbl2 where name= 'yyy'
)a;
  

Подзапрос возвращает результат в виде
00 00:01:54.252000
Но весь запрос возвращает ошибку как ORA-00932: несогласованные типы данных: ожидаемое ЧИСЛО получено С ИНТЕРВАЛОМ ОТ ДНЯ До СЕКУНДЫ.

Поймите, что для этого требуется что-то вроде этого

 SELECT COALESCE (
(to_timestamp('2014-09-22 16:00:00','yyyy/mm/dd HH24:MI:SS') - to_timestamp('2014-09-22 09:00:00','yyyy/mm/dd HH24:MI:SS')) -
(to_timestamp('2014-09-22 16:00:00','yyyy/mm/dd HH24:MI:SS') - to_timestamp('2014-09-22 09:00:00','yyyy/mm/dd HH24:MI:SS')), INTERVAL '0' DAY) FROM DUAL;
  

Как можно достичь вместе с подзапросами, которые извлекают данные из столбцов типа временной метки?

Вы не можете суммировать INTERVAL DAY TO SECOND в Oracle. Я думаю, что это один из самых популярных запросов на открытие функций.

Вы можете преобразовать TIMESTAMP в DATE значения, тогда результатом будет разница в днях. Умножьте на 24*60*60 , хотите ли вы получить секунды вместо:

 SELECT SUM(TIME_SPENT) * 24*60*60 FROM FROM
(
 select CAST(a AS DATE) - CAST(b AS DATE) as time_spent from tbl1 where name = 'xxx'
 union all
select CAST(d AS DATE) - CAST(d AS DATE) as time_spent from tbl2 where name= 'yyy'
);
  

Или вы можете написать функцию, которая преобразует INTERVAL DAY TO SECOND в секунды:

 CREATE OR REPLACE FUNCTION GetSeconds(ds INTERVAL DAY TO SECOND) DETERMINISTIC RETURN NUMBER AS
BEGIN
    RETURN EXTRACT(DAY FROM ds)*24*60*60 
          EXTRACT(HOUR FROM ds)*60*60 
          EXTRACT(MINUTE FROM ds)*60 
          EXTRACT(SECOND FROM ds);
END;
  

и используйте это так:

 SELECT SUM(TIME_SPENT), numtodsinterval(SUM(TIME_SPENT), 'second')
(
 select GetSeconds(a-b) as time_spent from tbl1 where name = 'xxx'
 union all
select GetSeconds(c-d) as time_spent from tbl2 where name= 'yyy'
);
  

попробуйте использовать приведенный ниже запрос

  SELECT sum(extract(second from time_spent)) FROM
 (
  select a - b as time_spent from test2 where name = 'xxx'
  union all
  select c - d as time_spent from tbl2 where name= 'yyy'
 )a;
  

Похоже, что столбец time_spent является типом метки времени в вашей таблице, и он не может передать правильный тип данных в функции Sum. Используйте функцию извлечения, чтобы получить секунды из time_spent.

 with t(a,b) as (
  select timestamp'2014-09-22 16:00:00.000', timestamp'2014-09-23 16:00:00.001' from dual union all
  select timestamp'2014-09-22 16:00:00.000', timestamp'2014-09-24 16:00:00.001' from dual union all
  select timestamp'2014-09-22 16:00:00.000', timestamp'2014-09-25 16:00:00.001' from dual union all
  select timestamp'2014-09-22 16:00:00.000', timestamp'2014-09-26 16:00:00.001' from dual union all
  select timestamp'2014-09-22 16:00:00.000', timestamp'2014-09-27 16:00:00.001' from dual
)
select
                        sum( (date'1-1-1' (b-a)*24*60*60 - date'1-1-1'))     as ssum_seconds_1,
                  round(sum( (date'1-1-1' (b-a)*24*60*60 - date'1-1-1')), 3) as ssum_seconds_rounded,
 numtodsinterval( round(sum( (date'1-1-1' (b-a)*24*60*60 - date'1-1-1')), 3), 'second') dsint
from t
/