Поле Oracle VARCHAR2 на сегодняшний день с substr

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

У меня есть:

ДД-ММ-ГГ чч:мм:сс.сссс

ДД-ПН-ГГ чч:мм:сс

ДД-ММ-ГГГГ чч:мм:сс.сссс

Все это хранится в поле varchar2. Теперь мне нужно выполнять поиск между диапазонами дат, и это вызывает у меня проблемы. Как я могу подойти к этому?

Вот некоторые фрагменты кода, которые я пробовал:

Стандартный substr работает хорошо, но я не могу учитывать разные форматы даты:

select substr(created_on, 1,9) as date2 from rtl.HK_Alerts

В идеале, если я смогу заставить это работать:

select to_date(created_on, 'dd-mon-yy') as date_convert from rtl.HK_Alerts

Тогда я могу сделать это:

select * from my_table 
    where to_date(created_on, 'dd-mon-yy') > '01-Jan-1970'
    and to_date(***strong text***created_on, 'dd-mon-yy') < '31-Jan-1970'

Кроме того, как мне учитывать разные форматы даты из разных систем? К сожалению, нет системного идентификатора, с которым я мог бы работать.

вот изображение некоторых дат: created_on_date

07-ЯНВ-19 01.53.47.702000000
07-ЯНВ-19 01.53.47.992000000
07-ЯНВ-19 01.53.48.186000000
07-ЯНВ-19 01.53.48.360000000-1.-ЯН-1.500000
097 .48.548000000
07-ЯНВ-19 01.53.48.709000000
07-ЯНВ-19 01.53.48.900000000
20-ЯНВ-19 22.49.30.801000000
20-ЯНВ-19 22.004.3
-JAN-19 22.49.33.968000000


sql oracle to-date varchar2
person vwdewaal    schedule 10.05.2019    source источник
comment
Пожалуйста, размещайте форматированный текст, а не изображения. Но все значения в этом изображении кажутся строками в одном и том же формате или фактической отметкой времени, которую ваш клиент форматирует для отображения. Пожалуйста, включите образцы данных, которые действительно показывают проблему, в свой вопрос (не в виде комментария) и в виде отформатированного текста. И фактический тип данных столбца в вашей таблице.   -  person Alex Poole    schedule 10.05.2019

Ответы (2)


arrow_upward
3
arrow_downward

Вы можете написать некоторую логику переключения, которая преобразует каждую строку метки времени в истинную метку времени, используя соответствующую маску. Вот пример:

SELECT
    created_on,
    CASE WHEN REGEXP_LIKE (created_on, '^[0-9]{2}-[0-9]{2}-[0-9]{4}')
         THEN TO_TIMESTAMP(created_on, 'DD-MM-YYYY HH24:MI:SS.FF') END AS 
         WHEN REGEXP_LIKE (created_on, '^[0-9]{2}-[0-9]{2}-[0-9]{2}')
         THEN TO_TIMESTAMP(created_on, 'DD-MM-RR HH24:MI:SS.FF')
         WHEN REGEXP_LIKE (created_on, '^[0-9]{2}-[A-Z]{3}-[0-9]{2}')
         THEN TO_TIMESTAMP(created_on, 'DD-MON-RR HH24:MI:SS')

created_on_ts ИЗ rtl.HK_Alerts;

введите здесь описание изображения

Демо

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

person Tim Biegeleisen    schedule 10.05.2019
comment
@AlexPoole Вы являетесь постоянным экспертом Oracle на этом сайте :-) ... Сначала я выдвинул четырехлетнюю модель, которая решает проблему, на которую вы указали. Лучше было бы использовать границы слов. Тогда порядок выражения CASE даже не будет иметь значения. - person Tim Biegeleisen; 10.05.2019

arrow_upward
3
arrow_downward

Возможно, вы слишком много думаете об этом. Преобразование даты в Oracle по умолчанию довольно гибкое, что одновременно и хорошо, и плохо. В этом случае, если у вас есть только показанные форматы, преобразование можно выполнить с помощью одной маски формата:

to_timestamp(created_on, 'DD-MM-RRRR HH24:MI:SS.FF')

Демонстрация с некоторыми выдуманными данными:

-- CTE for sample data
with hk_alerts (created_on) as (
            select '10-05-19 12:34:56' from dual
  union all select '10-05-19 12:34:56.789' from dual
  union all select '10-May-19 12:34:56' from dual
  union all select '10-May-19 12:34:56.789' from dual
  union all select '10-May-2019 12:34:56' from dual
  union all select '10-May-2019 12:34:56.789' from dual
)
-- actual query
select created_on, to_timestamp(created_on, 'DD-MM-RRRR HH24:MI:SS.FF') as date_convert
from hk_alerts;

CREATED_ON               DATE_CONVERT                 
------------------------ -----------------------------
10-05-19 12:34:56        2019-05-10 12:34:56.000000000
10-05-19 12:34:56.789    2019-05-10 12:34:56.789000000
10-May-19 12:34:56       2019-05-10 12:34:56.000000000
10-May-19 12:34:56.789   2019-05-10 12:34:56.789000000
10-May-2019 12:34:56     2019-05-10 12:34:56.000000000
10-May-2019 12:34:56.789 2019-05-10 12:34:56.789000000

Конечно, это по-прежнему должно предполагать, что любые строки, использующие названия/аббревиатуры месяцев, написаны на том же языке, который использует ваша сессия. Однако вы мало что можете с этим поделать.

Затем ваше сравнение может быть выполнено с литералами меток времени:

where to_timestamp(created_on, 'DD-MM-RRRR HH24:MI:SS.FF') >= timestamp '1970-01-01 00:00:00'
and to_timestamp(created_on, 'DD-MM-RRRR HH24:MI:SS.FF') < timestamp '1970-02-01 00:00:00'
person Alex Poole    schedule 10.05.2019
comment
правила преобразования строки в дату Oracle находятся в его документации. и вы также можете использовать DD-MM-YY HH24:MI:SS.FF9 в качестве модели формата. - person MT0; 10.05.2019
comment
@ MT0 - конечно, RR безопаснее, чем YY, если речь идет о двузначных годах? (Хотя согласитесь, что это может быть RR, а не RRRR.) - person Alex Poole; 10.05.2019
comment
Это действительно зависит от того, что означают данные OP. Является ли 01-JAN-70 годом 2070 или 1970, и может быть вариант использования для обоих (надеюсь, не смешанных в одной таблице), и OP должен потратить время, чтобы понять разницу между двумя моделями формата. - person MT0; 10.05.2019
comment
Что-то под названием created_on вряд ли будет иметь будущие даты, я надеюсь *8-) Но да, я согласен. - person Alex Poole; 10.05.2019