Преобразовать VARCHAR в формате 35:23:12 в тип данных, который я могу суммировать

ProgramBox

Преобразовать VARCHAR в формате 35:23:12 в тип данных, который я могу суммировать

Post author:admin
Запись опубликована:3 апреля, 2023
Post category:Вопросы по программированию

#sql #oracle

#sql #Oracle

Вопрос:

У меня есть данные varchar в формате ниже:

Пример 1 — 00:00:06

Пример 1 — 372:25:27

Он представляет часы: минуты: секунды

мне нужно иметь возможность преобразовать его в тип данных, который я могу суммировать, я хочу только часы и минуты. Есть ли способ преобразовать эти данные, чтобы я мог суммировать их с помощью запроса?

N.B я не могу изменить таблицы, выполняющие какую-либо работу в чужой БД.

1. пожалуйста, добавьте больше примеров и результатов, которые вы ожидаете

2. в приведенных мною примерах показано, как данные хранятся в БД, поэтому я хочу: Мои данные 372: 25:27 = 372 я могу работать только со значениями часов, не уверен, как я мог бы решить это, чтобы получить как часовой, так и минутный запрос, в основном, с числами перед первым :

Ответ №1:

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

 select 
regexp_substr('123:20:20' ,'(.*?)(:|$)', 1, 1, NULL, 1 ) HOUR,
regexp_substr('123:20:20' ,'(.*?)(:|$)', 1, 2, NULL, 1 ) MIN,
regexp_substr('123:20:20' ,'(.*?)(:|$)', 1, 3, NULL, 1 ) SECOND
from dual;

1. я получаю ошибку -ORA-01722: недопустимый номер 01722. 00000 — «недопустимый номер» * Причина: указанный номер был недействительным. * Действие: укажите допустимое число.

2. я скопировал неправильную ошибку, см. ниже имя столбца STATUSTRACKING ORA-01850: час должен быть между 0 и 23 01850. 00000 — «час должен быть между 0 и 23» * Причина: * Действие:

3. также это не время, а то, сколько времени что-то заняло, поэтому в моем примере 2-372:25:27 потребовалось 372 часа, 25 минут и 27 секунд

4. Было бы идеально, если бы я мог получить часы и минуты и способ их разделения, мне нужно рассчитать, сколько времени занимают процедуры, поэтому, если бы я мог получить часы в одном столбце и минуты в другом, было бы идеально!!!

Ответ №2:

Как показал @Walucas, вы можете использовать регулярные выражения для извлечения часовой, минутной и второй частей ваших строковых значений (при условии, что все они отформатированы одинаково):

 select statustracking,
  regexp_substr(statustracking ,'(.*?)(:|$)', 1, 1, null, 1) as h,
  regexp_substr(statustracking ,'(.*?)(:|$)', 1, 2, null, 1) as m,
  regexp_substr(statustracking ,'(.*?)(:|$)', 1, 3, null, 1) as s
from your_table;

STATUSTRACKING H         M         S        
-------------- --------- --------- ---------
00:00:06       00        00        06       
372:25:27      372       25        27

затем преобразуйте их в числа и умножьте элементы, чтобы все они представляли количество секунд (скажем), и добавьте их вместе для общего количества секунд, представленных строкой:

 select statustracking,
  3600 * to_number(regexp_substr(statustracking ,'(.*?)(:|$)', 1, 1, null, 1)) as h,
  60 * to_number(regexp_substr(statustracking ,'(.*?)(:|$)', 1, 2, null, 1)) as m,
  to_number(regexp_substr(statustracking ,'(.*?)(:|$)', 1, 3, null, 1)) as s,
  3600 * to_number(regexp_substr(statustracking ,'(.*?)(:|$)', 1, 1, null, 1))
      60 * to_number(regexp_substr(statustracking ,'(.*?)(:|$)', 1, 2, null, 1))
      to_number(regexp_substr(statustracking ,'(.*?)(:|$)', 1, 3, null, 1)) as total_s
from your_table;

STATUSTRACKING          H          M          S    TOTAL_S
-------------- ---------- ---------- ---------- ----------
00:00:06                0          0          6          6
372:25:27         1339200       1500         27    1340727

затем суммируйте эти итоги:

 select sum(
    3600 * to_number(regexp_substr(statustracking ,'(.*?)(:|$)', 1, 1, null, 1))
        60 * to_number(regexp_substr(statustracking ,'(.*?)(:|$)', 1, 2, null, 1))
        to_number(regexp_substr(statustracking ,'(.*?)(:|$)', 1, 3, null, 1))
  ) as total_s
from your_table;

   TOTAL_S
----------
   1340733

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

 select floor(total_s / 3600) as h,
       floor(mod(total_s, 3600) / 60) as m,
       mod(total_s, 60) as s
from (
  select sum(
      3600 * to_number(regexp_substr(statustracking ,'(.*?)(:|$)', 1, 1, null, 1))
          60 * to_number(regexp_substr(statustracking ,'(.*?)(:|$)', 1, 2, null, 1))
          to_number(regexp_substr(statustracking ,'(.*?)(:|$)', 1, 3, null, 1))
    ) as total_s
  from your_table
);

         H          M          S
---------- ---------- ----------
       372         25         33

Если вы хотите вернуться к своему строковому формату, вы можете объединить их обратно с двоеточиями, добавив нули влево с помощью lpad() или to_char() :

 select to_char(total_s / 3600, 'FM9999900')
       ||':'|| to_char(mod(total_s, 3600) / 60, 'FM00')
       ||':'|| to_char(mod(total_s, 60), 'FM00')
       as total_time
from (
  select sum(
      3600 * to_number(regexp_substr(statustracking ,'(.*?)(:|$)', 1, 1, null, 1))
          60 * to_number(regexp_substr(statustracking ,'(.*?)(:|$)', 1, 2, null, 1))
          to_number(regexp_substr(statustracking ,'(.*?)(:|$)', 1, 3, null, 1))
    ) as total_s
  from your_table
);

TOTAL_TIME      
----------------
372:26:33

db<>скрипта