Блог

Главная - Вопросы по программированию - Как рассчитать разницу в дате в снежинке?

Как рассчитать разницу в дате в снежинке?

#sql #snowflake-cloud-data-platform

Вопрос:

У меня есть эти два дня:

 BEFORE_DATETIME: 2021-09-02 09:41:00
AFTER_DATETIME: 2021-09-09 09:41:00

Мне нужно подсчитать разницу за эти два дня. Итак, в этом примере: 7 дней.Однако в BEFORE_DATETIME у меня есть некоторые значения, которые являются строковыми (плохие записи). Я продолжаю получать сообщение об ошибке, когда использую функцию DATEDIFF из-за этих записей.

Как я могу рассчитать разницу в дате и игнорировать плохие записи?

Ответ №1:

Вы можете использовать функцию TRY_TO_TIMESTAMP, которая проанализирует ваши входные данные как метку времени и вернет значение NULL в случае сбоя анализа.

Затем вы можете применить свой DATEDIFF по своему усмотрению.

 WITH cte AS (
  SELECT
     $1 AS before_datetime,
     $2 AS after_datetime
  FROM VALUES
  ('2021-09-02 09:41:00', '2021-09-09 09:41:00'),
  ('random_bad_record', '2021-09-09 09:41:00'),
  ('2021-09-02 09:41:00', 'random_bad_record')
)
SELECT
    TRY_TO_TIMESTAMP(before_datetime) as before_datetime,
    TRY_TO_TIMESTAMP(after_datetime) as after_datetime,
    DATEDIFF('days', TRY_TO_TIMESTAMP(before_datetime), TRY_TO_TIMESTAMP(after_datetime)) AS diff_in_days
FROM cte
;

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

Комментарии:

1. Здравствуйте, большое вам спасибо! Я думаю, что это может сработать, но у меня есть два столбца значений (тысячи записей), нужно ли мне вводить каждый из них в поле «ОТ ЗНАЧЕНИЙ»?

2. Конечно, нет, ЗНАЧЕНИЕ FROM здесь только для того, чтобы воспроизвести ваш вариант использования в одном удобочитаемом запросе. С вашей стороны вам просто нужно использовать последний запрос. Я обновил его, чтобы использовать МЕТКУ TRY_TO_TIMESTAMP в окончательном запросе и сделать его более удобным для вас.

3. Прошу прощения, вы правы! Это работает, большое вам спасибо! Очень ценю вашу помощь!

Ответ №2:

Если вы используете TRY_TO_DATE и значение «не удается проанализировать», вы получите значение null, таким образом, вы можете ввести результат этой попытки в DATEDIFF или использовать встроенный, если вы пропустите это, таким образом, что-то вроде:

 IFF(TRY_TO_DATE(before_datetime) IS NOT NULL AND TRY_TO_DATE(after_datetime) IS NOT NULL, 
    DATEDIFF('days', before_datetime, after_datetime),
    0) AS alias_name

замена 0 на то, что вы хотите, когда значения плохие. и т. Д

Ответ №3:

Я хотел бы добавить к обсуждению здесь этот момент:

У меня есть некоторые значения, которые являются строковыми (плохие записи).

Я видел противоречивые данные о дате и времени — взятые из разных источников или введенные человеком в разных форматах. Если у вас такие «плохие» даты, этот SQL UDF пригодится. Он попробует различные распространенные форматы даты и времени, прежде чем, наконец, попробует базовую метку TRY_TO_TIMESTAMP. Если ни один из них не сработает, он вернет значение NULL.

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

 create or replace function TRY_MULTI_TIMESTAMP(STR string)
returns timestamp
language SQL
as
$
    case
        when STR RLIKE '[A-Za-z]{3} \d{2} \d{4} \d{1,2}:\d{2}:\d{2}' then try_to_timestamp(left(STR, 20), 'MON DD YYYY HH24:MI:SS')
        when STR RLIKE '\d{1,4}-\d{1,2}-\d{2} \d{1,2}:\d{2}:\d{2} [A|P][M]' then try_to_timestamp(STR, 'YYYY-MM-DD HH12:MI:SS AM')
        when STR RLIKE '\d{1,2}/\d{1,2}/\d{4}' then try_to_timestamp(STR, 'mm/dd/yyyy')
        when STR RLIKE '\d{1,2}\/\d{1,2}\/\d{4} \d{1,2}:\d{2}:\d{2} [A-Za-z]{2}' then try_to_timestamp(STR, 'MM/DD/YYYY HH12:MI:SS AM')
        when STR RLIKE '\d{1,2}\/\d{1,2}\/\d{4} \d{1,2}:\d{2}' then try_to_timestamp(STR, 'MM/DD/YYYY HH24:MI')
        when STR RLIKE '[A-Za-z]{3}, \d{1,2} [A-Za-z]{3} \d{4} \d{1,2}:\d{1,2}:\d{1,2} [A-Za-z]{3}' then try_to_timestamp(left(STR, len(STR) - 4) || ' ' || '00:00', 'DY, DD MON YYYY HH:MI:SS TZH:TZM')   -- From Snowflake "LIST" command
        when STR RLIKE '\d{1,2}/\d{1,2}/\d{2} \d{1,2}:\d{2} [A|P][M]' then try_to_timestamp(STR, 'MM/DD/YY HH12:MI AM')
        when STR RLIKE '[A-Za-z]{3} [A-Za-z]{3} \d{2} \d{4} \d{1,2}:\d{2}:\d{2} GMT.*' then try_to_timestamp(left(replace(substr(STR, 5), 'GMT', ''), 26), 'MON DD YYYY HH:MI:SS TZHTZM')  -- Javascript
        else try_to_timestamp(STR) -- Final try without format specifier.
    end
$;

select try_multi_timestamp('Sat Oct 02 2021 17:53:40 GMT 0000 (Coordinated Universal Time)'); -- JavaScript date format
select try_multi_timestamp('Fri, 17 Apr 2020 17:55:45 GMT'); -- Snowflake LIST command file time
select try_multi_timestamp('Oct 26 2021 6:25:15');
Метки: