Как рассчитать общее время из столбца даты в Oracle

#sql #oracle #oracle11g

#sql #Oracle #oracle11g

Вопрос:

У меня есть таблица, в которой у меня есть такой журнал.

 select table_name,  
    min(to_char(DATO,'dd.mm.yyyy hh24:mi')) as start_time, 
    max(to_char(DATO,'dd.mm.yyyy hh24:mi')) as end_time
from TMP_AUDIT_LOG t
where FLAG='Y'
and TR_MODE='EXPORT'
group by T.TABLE_NAME
order by 2

и вывод выглядит следующим образом

 TABLE_NAME  START_TIME          END_TIME
----------  ----------------    ----------------
MAPTEXT     06.11.2020 17:29    06.11.2020 18:12
COMPONENT   06.11.2020 18:12    06.11.2020 19:18
SETTKONT    06.11.2020 19:18    06.11.2020 20:24
ROUTELOG    06.11.2020 20:24    06.11.2020 20:44
CROSSEC     06.11.2020 20:44    06.11.2020 20:49

Но я хочу иметь еще один столбец, который даст мне общее время, как показано ниже

 TABLE_NAME  START_TIME          END_TIME          TOTAL_TIME
----------  ----------------    ----------------  ----------
MAPTEXT     06.11.2020 17:29    06.11.2020 18:12  43m
COMPONENT   06.11.2020 18:12    06.11.2020 19:18  1h 6m
SETTKONT    06.11.2020 19:18    06.11.2020 20:24  1h 6m
ROUTELOG    06.11.2020 20:24    06.11.2020 20:44  24m
CROSSEC     06.11.2020 20:44    06.11.2020 20:49  5m

Я попытался уменьшить оба времени, но не получил должного результата.

 select table_name, start_time, end_time, 
(to_date(end_time,'dd.mm.yyyy hh24:mi')-to_date(start_time,'dd.mm.yyyy hh24:mi')) as total_time from (
select table_name,  
  min(to_char(DATO,'dd.mm.yyyy hh24:mi')) as start_time, 
  max(to_char(DATO,'dd.mm.yyyy hh24:mi')) as end_time
from TMP_EXPORT_WORKSPACE t
where FLAG='Y'
and TR_MODE='EXPORT'
group by T.TABLE_NAME
order by 2
)
;

Результат, который я получаю, приведен ниже

 TABLE_NAME  START_TIME          END_TIME          TOTAL_TIME
----------  ----------------    ----------------  ----------
MAPTEXT     06.11.2020 17:29    06.11.2020 18:12  0.0298611111111111111111111111111111111111
COMPONENT   06.11.2020 18:12    06.11.2020 19:18  0.0458333333333333333333333333333333333333
SETTKONT    06.11.2020 19:18    06.11.2020 20:24  0.0458333333333333333333333333333333333333
ROUTELOG    06.11.2020 20:24    06.11.2020 20:44  0.0138888888888888888888888888888888888889
CROSSEC     06.11.2020 20:44    06.11.2020 20:49  0.003472222222222222222222222222222222222222

1. min(to_char — Не преобразовывайте в текст для выполнения вычислений!

2. я тоже пробовал это .. min(to_date(to_char(DATO,’дд.мм.гггг чч24: mi’),’дд.мм.гггг чч24: mi’)) как start_time все еще не повезло

3. Какой тип dato ? DATE или TIMESTAMP ?

4. это только ДАТА

5. Это хорошо. DATE это неподходящее имя Oracle для datetime. Я не знаю, почему вы пытаетесь сделать это datetime строкой ( TO_CHAR ) или вообще сделать это datetime datetime ( TO_DATE ) . Это уже datetime. Именно то, что вам нужно. (То же самое, конечно, относится и к метке времени, которая также является datetime, только она обеспечивает более высокую точность и, возможно, часовые пояса.)

Ответ №1:

Не преобразовывайте даты в строки. Это не имеет смысла и приводит только к неожиданным результатам. Столбец dato — это DATE и для каждой таблицы, которую вы хотите использовать как минимум dato и максимум dato , поэтому просто используйте MIN(dato) и MAX(dato) . Тогда вам нужна разница. Вы получаете разницу, вычитая одно из другого:

 select
  table_name,  
  min(dato) as start_time, 
  max(dato) as end_time,
  max(dato) - min(dato) as total_time
from tmp_audit_log t
where flag = 'Y'
and tr_mode = 'EXPORT'
group by table_name
order by start_time;

Как dato и a DATE , вы получаете разницу в доли дней. Если вы хотите INTERVAL вместо этого преобразовать из DATE в TIMESTAMP :

 max(cast(dato as timestamp)) - min(cast(dato as timestamp)) as total_time